Data extraction from a cell!

JTevez · #1 10-07-2015, 03:05 AM

Hi guys,

I've been at this all morning and had numerous attempts. Please help

Basically, in single cellsI receive data in this format:

25km 50m
3km 700m
.....

In order for me to do my relevant calculations, I need to split them into numbers only. For example:

kilometreage km m
25km 50m 25 50

3km 700m 3 700

Please see attached attempts for where I'm at.

Thanks i advance

JT

gebobs · #2 10-13-2015, 11:55 AM

Couple ways to skin that cat...

Use Data: Text to Columns. You can just hit finish since you want space delimited and that is the default. This will split the data into two columns, side by side, with the units e.g. 5km and 120m. At this point, you can do find and replace on both columns to remove the units.

The other is to manipulate by formulas. These formulas to the right of your cells should do the trick. There are probably other formulas or even other strategies but these are what first came to mind. Let me know if you have any questions.

For data in A1, put these in B1 and C1:

Code:

=VALUE(LEFT(A1,FIND("km",A1)-1))

Code:

=VALUE(MID(A1,FIND(" ",A1)+1,FIND("m",A1,FIND(" ",A1))-FIND(" ",A1)-1))

JTevez · #3 10-14-2015, 11:56 PM

Hi Gebobs,

Thanks for that, works a treat! Made life a lot easier for me and my colleagues.

regards,

Jamie

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Data validation,force cell to be filed with number if respective cell is not empty	nicholes	Excel Programming	0	08-01-2015 09:08 AM
Copying data from one cell to another where condition is third cell	fairyca	Excel	4	03-30-2014 08:22 AM
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1	bobznkazoo	Excel	2	03-27-2014 11:14 AM
Bibliography creation and automatic extraction	styxsailor	Word	3	11-30-2012 02:42 PM
Email Extraction	dpad	Outlook	1	08-17-2010 06:01 PM