Splitting a cell data into seperate columns

[jpowell79]

Hi Guys,

Can anyone help me with a simple formula.

I have column A which contains data similar to the data below:

e.g. A1=
L9>1 268 Antigua and Barbuda

I have no idea what the L9> relates to, so the first thing I want to do is remove anything that is at the start of the cell, up to and including the >

The 1 268 is the Area code, so I'd like this value in cell B1

Finally I'd like the Country in cell C1

So for example, if we looked at the following cells

A1 = L9>1 268 Antigua and Barbuda
A2 = L240>1 340 Virgin Islands
A3 = L27>1 441 Bermuda
A4 = L88>1 473 Grenada

Then....

B1 = 1268
B2 = 1340
B3 = 1441
B4 = 1473

and

C1 = Antigua and Barbuda
C2 = Virgin Islands
C3 = Bermuda
C4 = Grenada



Hope that makes sense

Thanks Guys

 

[lenze]

Not elegant, but it might work!!!
In B1, Try

=MID(A1,FIND(">",A1,1)+1,5)

In C1

=RIGHT(A1,LEN(A1)-FIND(">",A1,1)-6)

Copy down

lenze

 

[jpowell79]

thanks lenze, but I'm afraid this doesn't work

It works for some cells, but for others, it creates weird results.

For example, a cell in column A that reads the following:

A37 = L100>36 Hungary


Should be converted to:

B37 = 36
C37 = Hungary


But your formula translates this to

B37 = 36 Hu
C37 = gary


I think your formula only appears to work for some cells, but its worth noting that the dialling codes vary in Length, as do the length of digits that preceed the >

Any more suggestions?

Thanks