#1
|
|||
|
|||
extract time from a cell and move it to front of cell before text
i have a big table with each cell having text with a different time (9:00), etc. in the middle of the text. i want to extract the time and move it to the front of the cell before the text. how can i do this? thanks
|
#2
|
|||
|
|||
Without seeing your data it's hard to say?
Can you post some sample data? |
#3
|
|||
|
|||
it looks like this....
john 9:00 doe jane 6:00 doe I want it to look like this... 9:00 john doe 6:00 jane doe is there something I can do, since it's a big document. it would take a long time to do it manually. |
#4
|
||||
|
||||
A quick method would be
Data - Text to columns - Delimited - use space as delimiters - Make sure you set the format to text for the second columns in the next screen - Finish You now have each string in three columns say B C and D n E enter =C1&" "&B1&" "&D1&" " Pull down as needed
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
You can try this in B1 copied down...
=TRIM(MID(A1,FIND(" ",A1),5))&" "&TRIM(SUBSTITUTE(A1,TRIM(MID(A1,FIND(" ",A1),5)),"")) It works for your sample data, but could be something else to consider. |
#6
|
|||
|
|||
I tried both things, but they didn't seem to work, unless i did something wrong. to be more specific, the text i have is not as simple as my example above. it actually looks more like this...
john bob harry 9:00 doe don jane 6:00 jill sam lara tim 5:00 jim can i do something to move the times to the front of the text in every line? |
#7
|
|||
|
|||
Maybe you can use the following absolutely awful array formula:
=TEXT(LOOKUP(9^9,1*MID(A1,MIN(IF(ISNUMBER(1*MID(A1 ,COLUMN(1:1),1)),COLUMN(1:1))),COLUMN(1:1))),"hh:m m")&" "&MID(A1,1,MIN(IF(ISNUMBER(1*MID(A1,COLUMN(1:1),1) ),COLUMN(1:1)))-1)&MID(A1,FIND(" ",A1,MIN(IF(ISNUMBER(1*MID(A1,COLUMN(1:1),1)),COLU MN(1:1))))+1,LEN(A1)) |
#8
|
|||
|
|||
Please note that there shouldn't be a space between U and M in the last COLU MN(1:1) and also between m and m in "hh m m"
That error must be due to something on this site. |
#9
|
|||
|
|||
xor, i pasted the array into b1 (the data is in a1), but it doesn't do anything. it just puts "#N/A" into b1 after i hit enter. am i doing something wrong?
|
#10
|
|||
|
|||
As I wrote, it is an array formula which means that when completing the formula you must hold down Ctrl and Shift before pressing Enter instead of just pressing Enter. If you do it correctly Excel will automatically put braces {} around the formula. Do not try to enter these braces manually.
|
#11
|
|||
|
|||
xor, that seemed to work man. thanks a lot!
now can you tell me how to delete the original data after i get the new column of data created with the formula. if i go to delete the original column, the new column with the times in the front of the cell get all messed up. or is there a way to use the formula in the second column and have it change the data in the first column instead of just creating the new data in a newly created second column? |
#12
|
|||
|
|||
Let's say you your data in A1:A100 and the formulas in B1:B100 then do the following:
Select B1, press F2 and put a ' in front of the equal sign and press Enter. Now take a copy of B1 and paste it to for example G1 (to keep a copy of the original formula). You can, if you prefer, put a ' also i H1 to avoid the text spill over to the right of G1. Now select B1:B100, take a copy and paste it as values to A1:A100. Select B1:B100 and press Delete. |
#13
|
|||
|
|||
thanks xor, it worked nicely. if i want to use that formula on a different column, like the C column instead of the A column, what all would i have to change in that formula?
|
#14
|
|||
|
|||
Just change all A1 to C1 and copy down.
|
#15
|
|||
|
|||
xor, i tried that, but it doesn't work. i pasted the formula into the 'd' column and tried to manipulate the data into the 'c' column, but all i get is #n/a in the 'd' column after running the formula. is there something else that needs to be changed?
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Move the data at bottom cell to right side of the cell | kcyag91 | Excel | 1 | 01-28-2016 12:28 AM |
I'm looking for a formula to extract all the text before the last name in a cell | Gef | Excel | 4 | 02-01-2015 07:14 PM |
How to extract text from a cell and preserve special formatting? | epid011 | Word VBA | 2 | 04-04-2014 04:54 PM |
Add asterisks in front of all bold sentences in a table cell | mrayncrental | Word VBA | 8 | 02-06-2014 11:48 PM |
Move data from 1 cell to another cell | Catalin.B | Excel | 1 | 06-25-2011 12:51 PM |