Extract all text from a string with numbers and characters

[lawi227]

Hello All,

I have a column of seat (arena) data. The data contains section numbers and rows. I want to extract the section numbers into 1 column and the row (letters) into another column.

Here's an example of my data set:
[TABLE="width: 368"]
<colgroup><col></colgroup><tbody>[TR]
[TD]104 BB[/TD]
[/TR]
[TR]
[TD]103 & 105 BB[/TD]
[/TR]
[TR]
[TD]104 CC[/TD]
[/TR]
[TR]
[TD]103 & 105 CC / 104 DD[/TD]
[/TR]
[TR]
[TD]103 & 105 DD / 107 BB / 106 & 102 BB, CC[/TD]
[/TR]
[TR]
[TD]103 & 105 FWA - FWC

There is no consistency in the formatting so I don't believe I can do "text to column." I'm also going to need to clean up the data after I separate the numbers and the letters.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

Check if this formula works well with your data (the formula is entered in cell D2 of Sheet 1 with Ctrl + Shift + Enter, not just Enter):

Excel Workbook
	A	B	C	D
1	Section	Row	Seat #	Amount
2	103	BB	3	18500
3	103	CC	3	17000
4	104	BB	3	19500
5	104	CC	3	18000
6	104	DD	3	17000
7	105	BB	3	18500
8	105	CC	3	17000
9	105	DD	3	17000
10	106	XX		NO DATA
Sheet

In post #5 there are double spaces between the data resulted. They can be substituted with UDFs which produce only one space between the data, but it is not neccessary when using the formula in this post.

 

[lawi227]

Check if this formula works well with your data (the formula is entered in cell D2 of Sheet 1 with Ctrl + Shift + Enter, not just Enter):

*	A	B	C	D
BB
CC
BB
CC
DD
BB
CC
DD
XX	*	NO DATA

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Section[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"]Seat #[/TD]
[TD="align: center"]Amount[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]103[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]18500[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]103[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]17000[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]104[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]19500[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]104[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]18000[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]104[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]17000[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]105[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]18500[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]105[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]17000[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]105[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]17000[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]106[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 {=IFERROR(INDEX(Sheet2!E$2:E$5,MATCH(1,ISNUMBER(SEARCH(A2,Sheet2!C$2:C$5))*ISNUMBER(SEARCH(B2,Sheet2!D$2:D$5)),0)),"NO DATA")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
In post #5 there are double spaces between the data resulted. They can be substituted with UDFs which produce only one space between the data, but it is not neccessary when using the formula in this post.

This worked great, except somehow whenever there is an error, #VALUE shows up instead of "Check Your Inputs" which I altered. I also multiplied E18, but that should have an impact. I also made sure to do CTRL+SHIFT+ENTER.

=IFERROR(INDEX('Ticket Info'!E$2:E$53,MATCH(1,ISNUMBER(SEARCH(F18,'Ticket Info'!C$2:C$53))*ISNUMBER(SEARCH(G18,'Ticket Info'!D$2:D$53)),0)),"Check Your Inputs")*E18


Any advice on how to fix it? It was working not that long ago.


EDIT:

Note: I just realized that by multiplying E18 to the equation, that is what is creating the VALUE problem.

Your equation works great for pulling the amount, however, if I buy 2 tickets, I need to take the amount that your formula pulls and multiply it by 2. The number of tickets is found in Column E.

 

[István Hirsch]

I think you need something similar; if it is the user who enters the number of tickets you should validate that the number entered is a positive integer.

Excel Workbook
	A	B	C	D	E	F
1	Section	Row	Seat #	Price/ticket	Number of tickets	Total
2	103	BB	3	18500	2	37000
3	103	CC	3	17000	1	17000
4	104	BB	3	19500	3	58500
5	104	CC	3	18000	4	72000
6	104	DD	3	17000	2	34000
7	105	BB	3	18500	1	18500
8	105	CC	3	17000	3	51000
9	105	DD	3	17000	2	34000
10	106	XX	3	Check Your Input
11	1030	CC	3	Check Your Input
Sheet

 

[lawi227]

I was trying to avoid having another column, but this will work! Thanks so much for your help! I learned some new formulas!

 

[István Hirsch]

You are welcome.