Help! Splitting Text And Numbers In The Same Cell

  • Thread starter Thread starter William
  • Start date Start date
W

William

I need the formula for splitting a string of data with both numbers and
text. The sample is W435T786M32J45 I want to split the data starting at
each of the letters. i.e (CELL B2) "W435" (CELL B3) "T786" (CELL B4)
"M32" (CELL B5) "J45". All the numbers are not the same length so I
have to allow for that. Please Help!!!! THANKS TO ALL
 
If there is no "rule" to it as if the numbers of characters can differ where
they are etc..
Then it is basically impossible using functions
However if there are only 3 letters and the rest are numbers (regardless of
how many) it is possible
Assuming that W435T786M32J45 is in B1, in B2 use

=LEFT(B1,MATCH(FALSE,ISNUMBER(--MID(B1,ROW(INDIRECT("2:"&LEN(B1))),1)),0))

in B3 use

=LEFT(SUBSTITUTE(B1,B2,""),MATCH(FALSE,ISNUMBER(--MID(SUBSTITUTE(B1,B2,""),R
OW(INDIRECT("2:"&LEN(SUBSTITUTE(B1,B2,"")))),1)),0))

in B4 use

=LEFT(SUBSTITUTE(B1,B2&B3,""),MATCH(FALSE,ISNUMBER(--MID(SUBSTITUTE(B1,B2&B3
,""),ROW(INDIRECT("2:"&LEN(SUBSTITUTE(B1,B2&B3,"")))),1)),0))


all the above formulas entered with ctrl + shift & enter

finally in B5 use

=SUBSTITUTE(B1,B2&B3&B4,"")

entered normally
 
Sorry, meant 4 letters [are only 3 letters ]..

--

Regards,

Peo Sjoblom

Peo Sjoblom said:
If there is no "rule" to it as if the numbers of characters can differ where
they are etc..
Then it is basically impossible using functions
However if there are only 3 letters and the rest are numbers (regardless of
how many) it is possible
Assuming that W435T786M32J45 is in B1, in B2 use

=LEFT(B1,MATCH(FALSE,ISNUMBER(--MID(B1,ROW(INDIRECT("2:"&LEN(B1))),1)),0))

in B3 use

=LEFT(SUBSTITUTE(B1,B2,""),MATCH(FALSE,ISNUMBER(--MID(SUBSTITUTE(B1,B2,""),R
OW(INDIRECT("2:"&LEN(SUBSTITUTE(B1,B2,"")))),1)),0))

in B4 use

=LEFT(SUBSTITUTE(B1,B2&B3,""),MATCH(FALSE,ISNUMBER(--MID(SUBSTITUTE(B1,B2&B3
,""),ROW(INDIRECT("2:"&LEN(SUBSTITUTE(B1,B2&B3,"")))),1)),0))


all the above formulas entered with ctrl + shift & enter

finally in B5 use

=SUBSTITUTE(B1,B2&B3&B4,"")

entered normally
Click to expand...
 
I need the formula for splitting a string of data with both numbers and
text. The sample is W435T786M32J45 I want to split the data starting at
each of the letters. i.e (CELL B2) "W435" (CELL B3) "T786" (CELL B4)
"M32" (CELL B5) "J45". All the numbers are not the same length so I
have to allow for that. Please Help!!!! THANKS TO ALL
Click to expand...

Assuming your original string is in B1, the following **array-entered**
formulas should do what you want:

B2: =MID(B1,1,MATCH(TRUE,ISERROR(-MID(B1,ROW(INDIRECT("2:255")),1)),0))

B3: =MID(RIGHT($B$1,LEN($B$1)-SUM(LEN($B$2:B2))),1,MATCH(TRUE,
ISERROR(-MID(RIGHT($B$1,LEN($B$1)-SUM(LEN($B$2:B2))),
ROW(INDIRECT("2:255")),1)),0))

Select B3 and copy/drag the formula down as far as might be needed. If you
drag further than needed, the formula should return a null length string in
those cells.

To **array-enter** a formula, after typing/pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces around the formula.


--ron
 
Peo Sjoblom said:
If there is no "rule" to it as if the numbers of characters can differ where
they are etc..
Then it is basically impossible using functions
Click to expand...
....

You should have known I'd respond. As long as I can use the defined name Seq
referring to =ROW(INDIRECT("1:1024")) I can extract the substring from the
beginning of the string to the end of the leftmost substring of numerals
using the array formula

=MID(A1,1,SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),1)-1)

and I can extract the n_th substring beginning after the (n-1)_th substring
of numerals and ending with the n_th substring of numerals using the array
formula

=MID(A1,SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),n-1),
SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),n)
-SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),n-1))

where the string being chopped up is in cell A1.

That said, this sort of thing is MUCH easier to do using a udf making use of
VBScript regular expressions.
 
Harlan Grove said:
...

You should have known I'd respond. As long as I can use the defined name Seq
referring to =ROW(INDIRECT("1:1024")) I can extract the substring from the
beginning of the string to the end of the leftmost substring of numerals
using the array formula

=MID(A1,1,SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),1)-1)

and I can extract the n_th substring beginning after the (n-1)_th substring
of numerals and ending with the n_th substring of numerals using the array
formula

=MID(A1,SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),n-1),
SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),n)
-SMALL(IF(ISNUMBER(-MID(" "&A1,Seq,1))
-ISNUMBER(-MID(A1,Seq,1))=1,Seq),n-1))

where the string being chopped up is in cell A1.

That said, this sort of thing is MUCH easier to do using a udf making use of
VBScript regular expressions.
Click to expand...

As long as I learn something from your response it is fine
and I certainly do whether it is irony/sarcasm or excel
does not matter <g>
 
If there is no "rule" to it as if the numbers of characters can differ where
they are etc..
Then it is basically impossible using functions
Click to expand...


Not so. I think my array-formula solution should work.


--ron
 
Peo Sjoblom said:
....
Try this
123456789abcdefg123456789abcdefghijklmnopqrstuvwxyz111111111111111111111111 1
1111111111
Click to expand...

Try it with my formulas. They seem to work.
 
Harlan Grove said:
Try it with my formulas. They seem to work.
Click to expand...


Yes. I changed the string a few times and tested your second formula and it
worked nicely
for all that I came up with..
 
Try this

123456789abcdefg123456789abcdefghijklmnopqrstuvwxyz1111111111111111111111111
1111111111
Click to expand...

Seems to work fine. But perhaps you made a different assumption than I as to
how the string should be parsed.

When he stated that he wanted "to split the data starting at each of the
letters", I assumed that each substring would have a maximum of one letter.

So here is what I get:

123456789
a
b
c
d
e
f
g123456789
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z11111111111111111111111111111111111

--ron
 
Ron Rosenfeld said:
1

Seems to work fine. But perhaps you made a different assumption than I as to
how the string should be parsed.

When he stated that he wanted "to split the data starting at each of the
letters", I assumed that each substring would have a maximum of one
Click to expand...
letter.


I am not disputing that, but you answered my post where I said:
"If there is no "rule" to it as if the numbers of characters can differ
where
they are etc..
Then it is basically impossible using functions"

I assumed the same as you but the "disclaimer" was meant
to explain that it is very difficult to parse strings
and given that it is almost impossible to adapt with a formula
unless there is some sort of pattern.
 
I assumed the same as you but the "disclaimer" was meant
to explain that it is very difficult to parse strings
and given that it is almost impossible to adapt with a formula
unless there is some sort of pattern.
Click to expand...

OK. I guess I thought your statement referred to any rules *except* what the
OP originally posted with regard to "starting at each of the letters."




--ron
 
Peo Sjoblom said:
to explain that it is very difficult to parse strings
and given that it is almost impossible to adapt with a formula
unless there is some sort of pattern.
Click to expand...
....

While text parsing by formula isn't something Excel is ideally suited to
perform, it's not almost impossible. The trick is finding expressions that
identify transitions as economically as possible. Also, there's need for
some basic, common building blocks such as an array of sequential integers
to feed to MID. There could also be defined names for numerals, letters,
token characters (letters, numerals and underscores), and various others.
With these, fairly complicated parsing may be perfomred within the nested
function call and maximum formula length limitations.
 
I think there is a very easy way to resolve. Go to data text to colum
and split all alpha numeric numbers into different colums. Then do
paste special and transpose. Then run a Data sort. Nos and labels ar
seperate!. A simple Macros recording can help
 
If you install the functions at
http://www.tmehta.com/regexp/add_code.htm
you can then select (at least) as many cells as needed to contain the
result and array-enter =RegExpFind(A1,"[a-zA-Z]\d+")
Click to expand...
...

Yeah, but pre-selection of an appropriately sized range is a PITA. Using my own
Subst udf wrapper around the VBScript RegExp object, I could put the formula for
the first match in cell A2 as

=subst(A$1,"([A-Za-z]*\d*){"&SUM(ROW()-ROW($A$2))&"}([A-Za-z]*\d*).*","$2")

and fill A2 down as far as needed. Note that I'm also allowing for multiple
letters anywhere, no letters at all at the beginning, and no numerals at the
end.
 
SORRY! To the people smarter than I am

I just wanted to give you more information; The letters come i
patterns. The sample (CELL B1) W435T786M32J45 I want to split this dat
string with the letter at the start of each column. i.e (CELL B2
"W435" (CELL B3) "T786" (CELL B4) "M32" (CELL B5) "J45".

(CELL C1) R23.4E89W34.7T1218M2378J0.0 (CELL C2) "R23.4" (CELL C3
"E89"(CELL C3) "W34.7" (CELL C4) "T1218" (CELL C5)"M2378" (CELL C6
"J0.0" The letters come in the same pattern in each string I am tryin
to split. There may be more letters added to the ends of the string
but they come in a pattern also, and do not change the pattern of th
letters in the middle of the string . The T still comes after the W
The W is always after the E with the numbers in between the letters.

There is only a single letter anywhere in the string no double letter
to contend with. But the numbers are of different digits in length
Some with 1 digit and some with 10 and anywhere between.
Thanks so much for your help!!!!!!
 
William > said:
I just wanted to give you more information; The letters come in
patterns. The sample (CELL B1) W435T786M32J45 I want to split this data
string with the letter at the start of each column. i.e (CELL B2)
"W435" (CELL B3) "T786" (CELL B4) "M32" (CELL B5) "J45".

(CELL C1) R23.4E89W34.7T1218M2378J0.0 (CELL C2) "R23.4" (CELL C3)
"E89"(CELL C3) "W34.7" (CELL C4) "T1218" (CELL C5)"M2378" (CELL C6)
"J0.0" The letters come in the same pattern in each string I am trying
to split. There may be more letters added to the ends of the strings
but they come in a pattern also, and do not change the pattern of the
letters in the middle of the string . The T still comes after the W.
The W is always after the E with the numbers in between the letters.
Click to expand...
....

So only the single letters R/E/W/T/M/J mark the beginning of each section
and some may be missing. There may be an optional set of ending characters
after the last of these letters present as well as their following numbers.
FTHOI, I'll assume those letters are "XYZ". Good. This is much easier to
handle. For the first one in cell B1, enter the following formula in B2.

B2:
=MID(B$1,
SMALL(FIND({"R","E","W","T","M","J","XYZ"},B$1&"XYZREWTMJ"),ROW()-1),
SMALL(FIND({"R","E","W","T","M","J","XYZ"},B$1&"XYZREWTMJ"),ROW())
-SMALL(FIND({"R","E","W","T","M","J","XYZ"},B$1&"XYZREWTMJ"),ROW()-1))

Fill B2 down as far as needed (into B3:B5). Fill B2 right into C2, then fill
C2 down as far as needed (into C3:C7).
There is only a single letter anywhere in the string no double letters
to contend with. But the numbers are of different digits in length.
Some with 1 digit and some with 10 and anywhere between.
Click to expand...

We don't always answer just your question. Thus the digression into multiple
letters. This would still be much easier to do using regular expressions,
for which there are now quite a few postings in this newsgroup's archives in
Google Groups. This would just change the match pattern to
"[REWTMJ][0-9.]+".
 
Maybe, you can show us how your method would work with the OP's
datasets (note, plural!)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top