Help! Splitting Text And Numbers In The Same Cell

  • Thread starter Thread starter William
  • Start date Start date
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"
Click to expand...

These array-entered formulas will do what you require:

To **array-enter** a formula, type or copy the formula into the cell, then hold
down <ctrl><shift> while hitting <enter>. Excel will place braces {...} around
the formula:

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

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

Copy/drag B3 down as far as needed. Then select B2:Bn, and copy/drag the
formulas to as many columns as you need. The cell references should adjust
properly if you do that.

The formula result will be a "null string" once you have parsed everything out.


--ron
 
Back
Top