splitting text to array,is it possible?

  • Thread starter Thread starter kang
  • Start date Start date
K

kang

the text is seperated by "_", like("abc_xyz_spq") and is not set how
many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc).
I want to do something to each text(abc,xyz,spq, etc).
How can I split them without VBA.
StringTokenizer-like function.
 
Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| the text is seperated by "_", like("abc_xyz_spq") and is not set how
| many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc).
| I want to do something to each text(abc,xyz,spq, etc).
| How can I split them without VBA.
| StringTokenizer-like function.
 
As I said earlier, it's possible to know how many time the token repeats.
so I prefer to splitting them to array.
 
It will work for any number of underscores (as long as you don't exceed Excel's limit for the number of columns)

Or do you mean something else?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| As I said earlier, it's possible to know how many time the token repeats.
| so I prefer to splitting them to array.
|
| Niek Otten wrote:
| > Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore
| >
 
kang,

Here is a formula solution.

For a string in A2, with an underscore character in cell A1 (your delimiter value), enter this into
cell B2

=SUBSTITUTE(A2,LEFT(A2,FIND($A$1,A2)-1)&$A$1,"")

and copy to C2:I2 - Note that this is eight columns, which I've assumed to be the maximum number of
delimited strings, but you can increase or decrease as appropriate.

Then in J2, enter the formula

=IF(ISERROR(SUBSTITUTE(A2,LEFT(A2,FIND($A$1,A2)-1)&$A$1,"")),IF(ISERROR(A2),"",A2),SUBSTITUTE(A2,$A$1&SUBSTITUTE(A2,LEFT(A2,FIND($A$1,A2)-1)&$A$1,""),""))

and copy to K2:Q2 (again, 8 columns)

and K2:Q2 will have the individual strings.

Then copy B2:Q2 down to match your values in column A, and then hide columns B to I.

HTH,
Bernie
MS Excel MVP
 
If you really want the values contained in an array,
then....assuming the letters are arranged in triplets separated by
underscores....

Try this:

With A1 containing the text to parse into an array

B1:
=MID(SUBSTITUTE(A1,"_",""),(ROW($A$1:INDEX($A:$A,(LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))+1),1))-1)*3+1,3)

If A1 contains abc_xyz_spq_pqr
the formula returns this array: {"abc";"xyz";"spq";"pqr"}

Is that what you're looking for?

***********
Regards,
Ron

XL2003, WinXP
 
Back
Top