Extracting Numbers from a Alphanumeric String

**martinhardy** · 03-31-2013, 03:46 PM

Hello,

I need to extract the numbers that appear in the attached CSV sample. This is what has been extracted from a database.
The numbers 1 to 12 at the end of the string are the months of the year. The numbers beside that are duty hours. So Person Doug Smith did 119:22 duty hours in January.

How can I extract the numbers into different columns?

Row Titles would be:

CREW NAME¦¦Jan¦¦Feb¦¦March¦¦etc
Doug smith
Total hours¦¦Jan

crewname;"time_format(SEC_TO_TIME(sum(TIME_TO_SEC(dutyhours))) '%H:%i')";"month(date)"
Douglas Smith;"119:22";"1"
Douglas Smith;"127:33";"2"
Douglas Smith;"138:31";"3"
Douglas Smith;"117:55";"4"
Douglas Smith;"133:27";"5"
Douglas Smith;"124:10";"6"
Douglas Smith;"152:35";"7"
Douglas Smith;"152:25";"8"
Douglas Smith;"90:53";"9"
Douglas Smith;"130:28";"10"
Douglas Smith;"123:06";"11"
Douglas Smith;"131:34";"12"
Simon Clare;"130:43";"1"
Simon Clare;"129:31";"2"
Simon Clare;"125:04";"3"
Simon Clare;"152:03";"4"
Simon Clare;"127:20";"5"
Simon Clare;"87:11";"6"

Would be great to get some help before tomorrow morning.

Thanks martin

**AndyLitch** · 03-31-2013, 03:59 PM

Put the following function in B2 then copy and paste it down through Column B

=MID(A2,FIND(";",A2,1)+2,FIND(";",A2,FIND(";",A2,1)+1)-FIND(";",A2,1)-3) & ":" & MID(A2,FIND(";",A2,FIND(";",A2,1)+1)+2,LEN(A2)-FIND(";",A2,FIND(";",A2,1)+1)-2)

Edit - Add on
The above will give you a number in the format 119:22:1
to extract the numbers to separate columns
C2 .... =VALUE(LEFT(B2,FIND(":",B2,1)-1))
D2..... =VALUE(MID(B2,FIND(":",B2,1)+1,FIND(":",B2,FIND(":",B2,1)+1)-FIND(":",B2,1)-1))
E2..... =VALUE(RIGHT(B2,LEN(B2)-FIND(":",B2,FIND(":",B2,1)+1)))

Then paste them all down through the length of the table.

**FDibbins** · 03-31-2013, 04:03 PM

Or you could high-light the range of data and use text-to-columns/delimited/semicolon

Andy's results will be text, so they will need to be converted to values before you can use them

**GazP** · 03-31-2013, 04:37 PM

Open file -> Import Wizard* -> Choose 'Delimited', click Next -> use ';' as the delimiter

*I think, I can't remember if that's what it's called.

**Ron Coderre** · 03-31-2013, 04:40 PM

Here's what I did to get what you're asking for...

• Change the file extension of the CSV file to .TXT
(it really isn't a Comma Separate Value file...It's a Semicolon Separated Value file)
• Import the file to Excel....Data.From_Text
...Check: Delimited...Click: Next
...Check: Semicolon..Click: Next
...Click: Finish
...Select a destination...Click: OK

• Edit the column headings, if necessary
• Select the data area
• Insert.Pivot_Table.Pivot_Table
...Choose a location...Click: OK
• Drag Crewname to ROWS
• Drag Month to COLUMNS
• Drag time_format to VALUES
...Click the dropdown and change the function from COUNT to SUM.

Now you have a pivot table with
...Months across the top
...Crewnames down the left side
...Hours/minutes for values
...Totaled at the bottom

Is that something you can work with?

**FDibbins** · 03-31-2013, 04:56 PM

Martin please do not post duplicate threads...

http://www.excelforum.com/excel-form...html?p=3179745

I have closed your other thread, you can continue on this 1