Text To Columns with odd items in brackets

M

mad3

Board Regular
Joined
Sep 15, 2009
Messages
128
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have two columns with 9 or more single digits numbers that are text. The issue with simple text to columns is that if any of the 9 numbers is a double digit number it looks like this in the cell...

Q​
R​
0001010(11)0400100000
0000001000000000200
101010100000000(10)000


I want to extract each individual number in column Q to separate columns; same with column R. to a separate column to study the totals, but have an issue every time it runs into the odd ball double digit number which is always like this (10)

If it helps, these are line scores from baseball games, but have a number of them with double digit scoring in a single inning.
any ideas on how to extract each number including the double digit numbers?

Thanks, Mike in Cali...
 
Sort by date Sort by votes
I have utilized the formulas and built the dynamic arrays. Now, I come to find I cannot utilize the spilled results in summing columns (innings). Is there a work around?
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
"I have utilized the formulas and built the dynamic arrays. Now, I come to find I cannot utilize the spilled results in summing columns (innings). Is there a work around?"

Which formula are you using?

Try coercing the text to a value; you may be able to just add double negatives to the front of the formula =--formula.
 
Upvote 0
As @Dave Patton has indicated, you can coerce the text values to numbers, but you also have to allow for the "x" values. So, using my formulas, you can leave the 'score' formulas as they were as I have for the left section and do the coercion in the sum formula or you could do the coercion in the 'score' formula as I have done on the right and then just use SUM as normal.

mad3.xlsm
TUVWXYZAAABACADAEAFAGAHAXAYAZBABBBCBDBEBFBGBHBIBJBKBL
3V-LINESCORE123456789101112131430H-LINESCORE12345678910111213
410000000010000000000121040x00121040x
5000100000000001000000000000(10)00001000001000001
6010203100010203100000011020000011020
700001100100001100100010510x00010510x
800130000000130000000011021x00011021x
92000020001200002000110000(30)000010000300000
10001012141001012141000103000000103000
1100000000000000000020112002x20112002x
122100101000001210010100000100020200100000002020010000
1300000000000000000000000100x00000100x
1400000101000000101000011100x00011100x
15010100010010100010000100001000100001
16004100000004100000000000010000000010
1700100000000100000000204101x00204101x
1800100001000100001002000010x02000010x
192000000202200000020210000012001000001200
2000000020000000020002100000x02100000x
21
2273883959230014451010549920100
Sheet1
Cell Formulas
RangeFormula
U4:AC4,U20:AC20,U19:AD19,U13:AC18,U12:AG12,U10:AC11,U9:AD9,U6:AC8,U5:AE5U4=LET(ts,TEXTSPLIT(T4,{"(",")"}),TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "))
AZ4:BH4,AZ20:BH20,AZ19:BI19,AZ13:BH18,AZ12:BL12,AZ10:BH11,AZ9:BI9,AZ6:BH8,AZ5:BJ5AZ4=LET(ts,TEXTSPLIT(AY4,{"(",")"}),n,TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "),IFERROR(--n,n))
U22:AG22U22=SUM(IFERROR(--U4:U20,0))
AZ22:BL22AZ22=SUM(AZ4:AZ20)
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
As @Dave Patton has indicated, you can coerce the text values to numbers, but you also have to allow for the "x" values. So, using my formulas, you can leave the 'score' formulas as they were as I have for the left section and do the coercion in the sum formula or you could do the coercion in the 'score' formula as I have done on the right and then just use SUM as normal.

mad3.xlsm
TUVWXYZAAABACADAEAFAGAHAXAYAZBABBBCBDBEBFBGBHBIBJBKBL
3V-LINESCORE123456789101112131430H-LINESCORE12345678910111213
410000000010000000000121040x00121040x
5000100000000001000000000000(10)00001000001000001
6010203100010203100000011020000011020
700001100100001100100010510x00010510x
800130000000130000000011021x00011021x
92000020001200002000110000(30)000010000300000
10001012141001012141000103000000103000
1100000000000000000020112002x20112002x
122100101000001210010100000100020200100000002020010000
1300000000000000000000000100x00000100x
1400000101000000101000011100x00011100x
15010100010010100010000100001000100001
16004100000004100000000000010000000010
1700100000000100000000204101x00204101x
1800100001000100001002000010x02000010x
192000000202200000020210000012001000001200
2000000020000000020002100000x02100000x
21
2273883959230014451010549920100
Sheet1
Cell Formulas
RangeFormula
U4:AC4,U20:AC20,U19:AD19,U13:AC18,U12:AG12,U10:AC11,U9:AD9,U6:AC8,U5:AE5U4=LET(ts,TEXTSPLIT(T4,{"(",")"}),TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "))
AZ4:BH4,AZ20:BH20,AZ19:BI19,AZ13:BH18,AZ12:BL12,AZ10:BH11,AZ9:BI9,AZ6:BH8,AZ5:BJ5AZ4=LET(ts,TEXTSPLIT(AY4,{"(",")"}),n,TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "),IFERROR(--n,n))
U22:AG22U22=SUM(IFERROR(--U4:U20,0))
AZ22:BL22AZ22=SUM(AZ4:AZ20)
Dynamic array formulas.
Click to expand...
Awesome! Worked perfectly. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
1K
Sektor
Sektor
R
  • Question Question
Golf scoring game
Replies
4
Views
558
Rock5150
R
M
  • Question Question
Cleaning up Code
Replies
0
Views
796
MHamid
M
J
  • Solved
Split data out over columns, under unique headings
Replies
3
Views
542
RasGhul
RasGhul
U
  • Question Question
The opposite: Delimited to formatted text
Replies
6
Views
1K
Legacy 98055
L

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top