Splitting a cell data into seperate columns

jpowell79

Active Member
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

Hope that makes sense

Thanks Guys

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

lenze

Legend
Not elegant, but it might work!!!
In B1, Try
Code:
``=MID(A1,FIND(">",A1,1)+1,5)``
In C1
Code:
``=RIGHT(A1,LEN(A1)-FIND(">",A1,1)-6)``

Copy down

lenze

jpowell79

Active Member
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

Replies
3
Views
551
Replies
9
Views
541
Replies
27
Views
2K
Replies
5
Views
544
Replies
1
Views
505

1,190,746
Messages
5,982,717
Members
439,791
Latest member
NwaTech_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back