Cell Splitting Formula

Hi all,

I am new to excel formulas, and have been trying to split a cell but just dont have the know how on how to do it.

What I am trying to do is split a cell with data like this:

cell A1: Devon 50.50N 03.55W SX9068

in to four cells like this:

cell A1: Devon
cell A2: 50.50N
cell A3: 03.55W
cell A4: SX9068

another example:

cell B1: Sir Gaerfyrddin (Carmarthenshire) 51.68N 04.14W SN5201

to

cell B1: Sir Gaerfyrddin (Carmarthenshire)
cell B1: 51.68N
cell B1: 04.14W
cell B1: SN5201

Ideally I need this to work on an entire column and not just one cell at a time. any feedback is very welcome.

Wouldn't it be cleaner to break the values out from A1 into A1,B1,C1,D1, then repeat all the way down the column? Much simpler, too, from a programming standpoint.

This it the Programming Forum, but your question sounds like you want a formula. Did you mispeak or mispost?

Create a sample workbook with 4-5 of these sample strings as they exist in your data in the correct placement.

Then add an AFTER sheet to the workbook showing the same data split the way you want including the stuff you're going to do next. If we're going to construct a macro to split, it can most like make the insertions and such you need as well.

None of those example include the parenthetical examples from the original post.

Also, we're just dropping the last part of each string?

Try this:

Please Login or Register  to view this content.

I believe I have sussed it, but I got a Run Time Error '9': Sub Script out of range error: .Range("D" & Rw) = Trim(MyArr(UBound(MyArr) - 1))

It's already IN the sample sheet I uploaded, just press F8 and run it.

============
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

Try not to quote my entire post, just use the quick reply instead of quote.

Originally Posted by Pauled

I believe I have sussed it, but I got a Run Time Error '9': Sub Script out of range error: .Range("D" & Rw) = Trim(MyArr(UBound(MyArr) - 1))

Please provide the value of RW at the time of debugging. Also, what is the string being evaluated?