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

Without seeing your data it's hard to say?

Can you post some sample data?

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.

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

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.

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?

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))

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.

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?

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.

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?

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.

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?

Just change all A1 to C1 and copy down.

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?