Splitting text string using formula...

I think you can do it VBA...

something along the lines of:

v = Split(cellref.Value, ",")

Then plant V into wherever you want to see the split:

For X = v(1)
For D = v(2)
For A = v(3)
For J = v(4)

These values refer to the position of the value in the now broken string....

I have seen it used on the openArgs of a userform in Access, not sure about excel though...

Thanks.

Yes there is a similar way but it uses VBA code not an excel formula


Regards

Goto Visual Basic Editor and insert a module and paste the function.
Then in the cell where you need specified data type the formula as:
=SplitMyString(A1,1,",")
This will give you first part (before 1st ",")

Using native excel fomula

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMNS($B1:B1)*LEN($A1))-LEN($A1),LEN($A1)))

Where A1 holds the string, B1 holds the formula, then drag and fill right as needed.

Dan1000 said:

I need to use a formula to split a text string using the commas as dividers with no spaces at either end of each string.

Maximum 4 commas to each string.

Is there a straight forward way of doing this rather than using the data to ccolumns excel command?

Regards

Click to expand...

Here's another one...

A2 = some string

Entered in B2 and copied across until you get blanks:

=TRIM(MID(SUBSTITUTE(","&$A2&REPT(",",6),",",REPT(" ",255)),COLUMNS($B2:B2)*255,255))

Thanks T.Valko.

That may be very useful!


Regards

taurean said:

Goto Visual Basic Editor and insert a module and paste the function.

Code:

Public Function SplitMyString(ByVal Rng As Range, ByVal iPos As Integer, ByVal sDelimiter As String) As String
Dim vSplit As Variant
Application.Volatile
vSplit = Split(Rng.Value, sDelimiter)
SplitMyString = vSplit(iPos - 1)
End Function

Then in the cell where you need specified data type the formula as:
=SplitMyString(A1,1,",")
This will give you first part (before 1st ",")

Click to expand...

Hi Shrivallabha

Remark:
Application.Volatile makes the function inefficient and should only be used if absolutely necessary which is not the case.

Dan1000 said:

Thanks T.Valko.

That may be very useful!


Regards

Click to expand...

You're welcome!

Here is a generic formula for obtaining the Nth field in a delimited text string...

=IF($A1="","",IF(fieldnumber=1,LEFT($A1,FIND(delimiter,$A1)-1),TRIM(MID(SUBSTITUTE($A1,delimiter,REPT(" ",LEN($A1))),LEN($A1)*(fieldnumber-1),LEN($A1)))))

Simply replace the two occurrences of the word "delimiter" with the text that is delimiting the fields (a comma for your current question) and replace the two occurrences of the word "fieldnumber" with the number of the field you want (1 is the field before the first comma, 2 is the field before the second comma, etc.). So, if A1 held the text you wanted to parse and B1 contained the field number you wanted, then this is the formula you would use for you comma delimited question...

=IF($A1="","",IF(B1=1,LEFT($A1,FIND(",",$A1)-1),TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),LEN($A1)*(B1-1),LEN($A1)))))