Parse data from one cell to multiple cells

I need to prepare a lot of data for upload from Excel to a system. Example, In cell A1: I have very long text that I need to parse into B1, C1, D1, etc, depending on the length as each cells can support up to 40 characters only. It seems easy as I can just find the length of A1 and use mid to parse them to the rest of the cells. But the sales person wants the data to be readable, meaning that I can't just split the data simply based on the character limits.

I have put a sample in the google docs at the link below. Can this be done using worksheet functions or I have to use a macro to do it?

http://spreadsheets.google.com/ccc?k...wVzVjQWc&hl=en

Thanks.

You could use native functions but they could get messy pending length of string.

If you wanted a UDF (VBA) then you could perhaps use something along the lines of:

Please Login or Register  to view this content.

the above, stored in standard Module, could be called from a cell along the lines of:

B12:
=SPLITSTRING($A13,40,COLUMNS($B12:B12))
and copied across horizontally as far as required

where A13 is the original string, 40 is the max len of any given string and the use of COLUMNS is to determine which "element" is being returned ... 1st, 2nd etc... there is an optional 4th parameter which would be the delimiter - where not specified it is assumed to be space... if you want to use multiple punctuation points as valid delimiters (comma etc) let us know.

You don't specify what should happen if the string exceeds max len and there is no delimiter present...

(and I have no doubt there are better methods out there...)

It would be easier if you uploaded your file here directly (via paperclip icon in reply window - if you can't see it - click Go Advanced)

Also outline expected results etc...

Not entirely sure I can follow the logic you applied to your expected results but here would be adapted UDF

Please Login or Register  to view this content.

The above will essentially look for a max of 2 delimiters rather than 1 as before (see vDelims)

The reason you did not get results previously was because the specified delimiter (comma) could not be located within the string within the specified number of chars (argument 2 of the function - eg 40 - must be found within 40 characters)

The above is an adjusted version such that should you opt for any delimiter other than "space" it will add space as a secondary delimiter by default such that if the primary delimiter can not be found (eg comma) it will then try again but this time split based on the second delimiter... if neither are found within the spec. number of chars it won't return anything.

The function is called from the cell exactly as before.

Acutally - I think I've worked out what you're doing in expected results - you're saying that if the primary delim can be found in the string then even if the entire string is less than the max permitted you still want to split accordingly.

The below (I believe) replicates your desired output with comma delimiter.
(note you would need to correct SINO entry as it's not consistent)

Please Login or Register  to view this content.