Copying and Separating out delimited fields

I need to find a way to take a string exported from database where fields are separated by a special character and repeat the line while separating out the fields which have been strung together.
e.g.CI-5210-410*15918ý15920
and separate to two lines
CI-5210-410*15918
CI-5210-410*15920
Need to repeat this with many lines and on some lines 10-20 different separations
Any suggestion?

Hello, Welcome to the forum.

Used 2 helper columns in E & F. Also used a dynamic range.

See the 2nd tab.

Activate the sheet contains data, then you need to define these named ranges. Press Control+F3, then click New.

Name: Length
Refers to: MATCH(REPT("Z",255),$A:$A)
Click OK

Change A:A to the first col in the real data, assume A:A contains only text entries, not numbers. Numbers with alphabets fine.

Again, click New

Name: Width
Refers to: =MATCH(REPT("Z",255),$1:$1)
OK

Change 1:1 to the first Row in the real data, assume row 1:1 contains only text entries, not numbers. Numbers with alphabets fine.

Again click New

Name: Data
Refers to: =OFFSET($A$1,,,Length,Width)
OK

Change A1 to the first heading in the data. here assume heading starts on A1, then A2 to down the data.

Use the 2 helper columns end of the data in this sequence. Row Position, then Total Numbers.

Eg: If your data range is A:Z use A:A for Row Position & AB for Total Numbers.

Copy the heading to a new sheet, say A3, A4 to across use the formula shown in the sample.

The last column formula Field4 use where the heading of the data contains ý for all other columns use the A4 formula.

Or attach your data heading with dummy data.

Replace D4, formula with this then copy down.

Please Login or Register  to view this content.