Seperate Text in Cells

[maic15]

I have cells that contain the following data:

Air Filtration Management Inc 00511578
Airport Diner 00510263
Al Prueitt & Sons 00511199
Albrecht Inc 00506438
Alan S. Roseberg Assoc., Inc. 00505659
Alert-All Corporation 00501902


I need to seperate the data into two different cells. Text to columns will not work. Does anyone know another way.

 

[erik.van.geit]

Hi, maic

it seems to eme you want to seperate the last 8 characters.
is this correct?

kind regards,
Erik

 

[maic15]

yes

 

[erik.van.geit]

OK, then use "left" and "right"
just like this

TAKE CARE: the sourcedata may not have leading or ending spaces)
else apply TRIM(A5) instead of A5

kind regards,
Erik

Map1.xls
	A	B	C	D
5	AirFiltrationManagementInc00511578	AirFiltrationManagementInc	00511578
6	AirportDiner00510263	AirportDiner	00510263
7	AlPrueitt&Sons00511199	AlPrueitt&Sons	00511199
8	AlbrechtInc00506438	AlbrechtInc	00506438
9	AlanS.RosebergAssoc.,Inc.00505659	AlanS.RosebergAssoc.,Inc.	00505659
10	Alert-AllCorporation00501902	Alert-AllCorporation	00501902
Blad1

 

[PA HS Teacher]

If it is always that you want the right 8 characters, and the rest in two different columns, then in column B use.
=Left(A1,Len(A1)-8) for the left part.

In Column C you could use.
=Right(A1,8) for the right part.

 

[erik.van.geit]

Or you can use code:
I prefer "-9" to get rid of the space
If you want to erase the first column then use no offset for the first line
ccc.Value =
and for the second line
ccc.Offset(0, 1)

kind regards,
Erik

Sub put_in_2_cols()
For Each ccc In Range(Range("A5"), Range("A65536").End(xlUp))
ccc.Offset(0, 1) = Left(ccc, Len(ccc) - 9)
ccc.Offset(0, 2) = Right(ccc, 8)
Next ccc
End Sub