Hiii Excel Expert,

Let's say I have column A - address, and I have to divide it into 3 columns as each cell should contain max 30 characters (or digits)

Is there a specific formula to split the address into 2 or 3 columns?

Given:
Column A - Address over 90 characters
Column B = empty but only allows 30 characters
Column C = Idem Column B

To do:
Split address over 3 columns so each column contain max 30 characters.

For Example: a superlong address with flat and blok and tower name as well as district name

THanks a lot!!

lily,
Are you trying to split up some exciting data or are you wanting to force the split upon entry?

Is the end result to be spread over say A1, B1, C1.
Bear in mind that A cannot contain the original long string and then the first 30 characters by using formulas. You can however use formulas to split A out into B,C & D
Are you ok using vba or a user defined function?
Is this a one off conversion?

Note also that splitting by character length albeit to the nearest space character will not otherwise split the address into 'logical' readable chunks.

Eg Flat 12, Block B Centenary Tower, Sometown Someplace

wouldsplit...

Flat 12, Block B Centenary - Tower, Sometown Someplace

Hey Snakeships,

I want to split up some exciting data.
THe end result is to be spread over 2 or 3 columns as long as the string per cell contain max 30 characters.

Using formula to split A into B, C & D is a good solution.
I am not familiar with vba or any user defined function.
This is a one off conversion.

It doesn't matter if the addresses aren't split in "logical" readable chunks.

THank you for your help.

lily,

Try the following e.g. for A1 split into B1,C1,D1.
In B1....
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ","99"),31-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))),99))

In C1....
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,B1,"",1)," ",REPT(" ","99"),31-LEN(SUBSTITUTE(LEFT(SUBSTITUTE(A1,B1,"",1),31)," ",""))),99))

In D1.....
=TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(A1,B1,"",1)),C1," ",1))

Should give max 30 characters per first two cells and then the balance in third cell. The split will depend upon the occurrence of spaces / the length of words at or around the split points. If text in A1 is close to 90 characters and the words on the split points are long then you could find that the balance of characters left to go in D is greater than 30????

Try it on your real data and see how it goes.

Once split out by the formulas if you wish, you can convert the results to 'hard' text by doing a Copy >> Paste Special >> Paste Values in situ.

Hi Snakeships!! Wow, it works. THank you. I just have to make sure none of the cells contain over 30 characters, because the system can otherwise not generate the excel file.

Thanks a lot