split cells

[LMA]

hello,

I have a dataset like this:

18abc.xls
18abd.xls
18ace.xls
19abi.xls
19bce.xls
20cde.xls

And I want to split it to something like this:

18 abc
18 abd
18 ace
19 abi
19 bce
20 cde

can you help me? I know a bit of VBA but would prefer to keep it in excel.

Thanks

[martindwilson]

how exactly do they look?
obvious thing would be =SUBSTITUTE(A1,".xls","")
if you want a space
=LOOKUP(10^10,--LEFT(A1,ROW($1:$99)))&" "&SUBSTITUTE(MID(A1,LEN(LOOKUP(10^10,--LEFT(A1,ROW($1:$99))))+1,255),".xls","")

[LMA]

Hey Martin

Thanks a lot. I was not clear enough. What I want to do is the following =>

I have a dataset like this:

18abc.xls
18abd.xls
18ace.xls
19abi.xls
19bce.xls
20cde.xls

And I want to split it to something like this:

18 (next cell) abc
18 (next cell) abd
18 (next cell) ace
19 (next cell) abi
19 (next cell) bce
20 (next cell) cde

[martindwilson]

slightly more straight forward
b1 =
LOOKUP(10^10,--LEFT(A1,ROW($1:$99)))
c1 =
SUBSTITUTE(SUBSTITUTE(A1,B1,""),".xls","")

[LMA]

Hey Martin

Sorry but I managed to solve it myself. The solution was obvious. Embarrassing :-) but text to column was sufficient.

Anyways - thanks for you help