Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-09-2017, 01:27 PM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-09-2017, 04:48 PM
jeffreybrown jeffreybrown is offline extract time from a cell and move it to front of cell before text Windows Vista extract time from a cell and move it to front of cell before text Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Without seeing your data it's hard to say?

Can you post some sample data?
Reply With Quote
  #3  
Old 01-10-2017, 12:21 AM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 01-10-2017, 01:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline extract time from a cell and move it to front of cell before text Windows 7 64bit extract time from a cell and move it to front of cell before text Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,584
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 01-10-2017, 05:46 AM
jeffreybrown jeffreybrown is offline extract time from a cell and move it to front of cell before text Windows Vista extract time from a cell and move it to front of cell before text Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #6  
Old 01-10-2017, 07:20 AM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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?
Reply With Quote
  #7  
Old 01-10-2017, 08:10 AM
xor xor is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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))
Reply With Quote
  #8  
Old 01-10-2017, 08:14 AM
xor xor is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #9  
Old 01-10-2017, 09:55 AM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 01-10-2017, 02:36 PM
xor xor is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #11  
Old 01-10-2017, 07:36 PM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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?
Reply With Quote
  #12  
Old 01-10-2017, 08:31 PM
xor xor is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #13  
Old 01-11-2017, 03:34 PM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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?
Reply With Quote
  #14  
Old 01-11-2017, 10:06 PM
xor xor is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Just change all A1 to C1 and copy down.
Reply With Quote
  #15  
Old 03-17-2017, 09:28 PM
ewso ewso is offline extract time from a cell and move it to front of cell before text Windows 10 extract time from a cell and move it to front of cell before text Office 2016
Advanced Beginner
extract time from a cell and move it to front of cell before text
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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?
Reply With Quote
Reply

Thread Tools
Display Modes


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
extract time from a cell and move it to front of cell before text 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
extract time from a cell and move it to front of cell before text 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:38 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft