Moving data from one column to another

[jj1972]

I need to work out how to remove the the data from the column in left hand side so that it is shown in the right hand column.
The data highlighted in black in the first column will vary in length. I do not require any data in the first column which is shown after the first space.
i.e I only need data 102.194 from the first column and moved into the second.
See Screenshot (2nd one i.e last one as 1st one is wrong)

Also , when l move the data across to the right column l need to remove the full stop as this character is NOT required, so 102.194 should be 102194.

[mbarron]

Try this:
=SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","")

If this does not solve your problem, please post a workbook with simple data and the desire result. Workbooks are much easier to work with than screen shots.

[prasad]

One probable solutions is to split data using "Text to Cloumns" options and then move the column to desired locations.

[jj1972]

[quote name='mbarron' post='793288' date='14-Sep-2009 15:55']Try this:
=SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","")

If this does not solve your problem, please post a workbook with simple data and the desire result. Workbooks are much easier to work with than screen shots.[/quote]


That works fine, but the problem is is that there is a space before the data starts see attached spreadsheet for example.
If you could resolve this , that would be great.

[mbarron]

Can you post the book saved to an earlier version of Excel. I do not have access to E2007

[jj1972]

[quote name='mbarron' post='793363' date='15-Sep-2009 13:37']Can you post the book saved to an earlier version of Excel. I do not have access to E2007[/quote]


see atatched.

[mbarron]

T..... problem is is that there is a space before the data starts .....

Based on your sheet the Trim function will solve this.

=SUBSTITUTE(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),".","")