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]

You mean you want all the numbers in one cell, and all the letters in another?

 

[Gerald Higgins]

Yeah, what exactly do you want the results to be ?

 

[lawi227]

[TABLE="class: cms_table, width: 368"]
<tbody>[TR]
[TD]104 BB

• 104
• BB

[/TD]
[/TR]
[TR]
[TD]103 & 105 BB

• 103, 105
• BB

[/TD]
[/TR]
[TR]
[TD]104 CC

• 104
• CC

[/TD]
[/TR]
[TR]
[TD]103 & 105 CC / 104 DD

• 103, 105, 104
• CC, DD

[/TD]
[/TR]
[TR]
[TD]103 & 105 DD / 107 BB / 106 & 102 BB, CC

• 103, 105, 107, 106, 102
• DD, BB, CC

[/TD]
[/TR]
[TR]
[TD]103 & 105 FWA - FWC

• 103, 105
• FWA - FWC

Do you get the picture? Sorry if I'm not clear - but i just need the numbers in 1 column and the letters in another column. The numbers and letters would ideally be separated by "," but they do not have to appear this way.

My objective is to do an index match function to look up ticket prices based on column 1 (section #) and column 2 (Row letter).[/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

Copy this two functions in an empty module:

Function SepNums(s As String) As String
a = Split(s)
For i = 0 To UBound(a)
If Not IsNumeric(a(i)) Then a(i) = ""
Next
SepNums = Trim(Join(a))
End Function

Function SepAlpha(s As String) As String
s = Trim(Replace(Replace(s, "/", " "), ",", " "))
b = Split(s)
For i = 0 To UBound(b)
If Not b(i) Like "[A-Z][A-Z]" And Not b(i) Like "[A-Z][A-Z][A-Z]" Then b(i) = ""
Next
SepAlpha = Trim(Join(b))
End Function

If the data begins in A1, enter SepNums(A1) in B1 and SepAlpha(A1) in C1:

Excel Workbook
	A	B	C
1	104 BB	104	BB
2	103 & 105 BB	103 105	BB
3	104 CC	104	CC
4	103 & 105 CC / 104 DD	103 105 104	CC DD
5	103 & 105 DD / 107 BB / 106 & 102 BB, CC	103 105 107 106 102	DD BB BB CC
6	103 & 105 FWA - FWC	103 105	FWA FWC
Sheet

 

[lawi227]

This worked great. I'm going to have to handtype a few sections that are in a range (e.g., 110-112).

I was using this index match formula (=INDEX('Ticket Info'!E:E,MATCH('16-17 M'!F14&G14,'Ticket Info'!C:C&'Ticket Info'!D:D,0))*E14) and it no longer works whenever there is more than one number in a column. For example, if you look at your table, my formula will not work for row 2. If I try to look up section 103 and row BB, it will return a "#N/A".

Any advice on this? How can I change my equation so that it just has to match part of the cell? Or am I going to have to separate each number into its own cell? - I would prefer not to do this; it would require a lot of texts and cells.

Thanks!

Copy this two functions in an empty module:

Function SepNums(s As String) As String
a = Split(s)
For i = 0 To UBound(a)
If Not IsNumeric(a(i)) Then a(i) = ""
Next
SepNums = Trim(Join(a))
End Function

Function SepAlpha(s As String) As String
s = Trim(Replace(Replace(s, "/", " "), ",", " "))
b = Split(s)
For i = 0 To UBound(b)
If Not b(i) Like "[A-Z][A-Z]" And Not b(i) Like "[A-Z][A-Z][A-Z]" Then b(i) = ""
Next
SepAlpha = Trim(Join(b))
End Function

If the data begins in A1, enter SepNums(A1) in B1 and SepAlpha(A1) in C1:

*	A	B	C
104 BB	BB
103 & 105 BB	103 *105	BB
104 CC	CC
103 & 105 CC / 104 DD	103 *105 * 104	CC * *DD
103 & 105 DD / 107 BB / 106 & 102 BB, CC	103 *105 * 107 * 106 *102 *	DD * *BB * * *BB *CC
103 & 105 FWA - FWC	103 *105 *	FWA *FWC

<colgroup><col style="width:30px; "><col style="width:319px;"><col style="width:150px;"><col style="width:113px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]104 [/TD]

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

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

[TD="align: right"]104 [/TD]

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

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

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

</tbody>

Spreadsheet Formulas

Cell Formula B1 =SepNums(A1) C1 =SepAlpha(A1) B2 =SepNums(A2) C2 =SepAlpha(A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[István Hirsch]

Please provide a description how you are going to further process the data in columns B and C in my table (not in terms of your formula) and attach columns with the desired results produced manually for each different data type you work with.

 

[lawi227]

Please provide a description how you are going to further process the data in columns B and C in my table (not in terms of your formula) and attach columns with the desired results produced manually for each different data type you work with.

Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD]Column I[/TD]
[/TR]
[TR]
[TD]Section[/TD]
[TD]Row[/TD]
[TD]Seat #[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]CC[/TD]
[TD]3[/TD]
[TD]17000
[/TD]
[/TR]
</tbody>[/TABLE]
(Note: The information starts on row 3)

I want to input information into Columns F and G and populate the Amount in Column I.

This information would pull from Sheet 2 (which is the sheet you helped create a formula for. The information looks like this:

Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]Ticket information (not sorted)[/TD]
[TD]Section[/TD]
[TD]Row[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]104 BB[/TD]
[TD]104[/TD]
[TD]BB[/TD]
[TD]19500[/TD]
[/TR]
[TR]
[TD]103 & 105 BB[/TD]
[TD]103 105[/TD]
[TD]BB[/TD]
[TD]18500[/TD]
[/TR]
[TR]
[TD]104 CC[/TD]
[TD]104[/TD]
[TD]CC[/TD]
[TD]18000[/TD]
[/TR]
[TR]
[TD]103 & 105 CC / 104 DD[/TD]
[TD][TABLE="width: 187"]
<colgroup><col></colgroup><tbody>[TR]
[TD]103 105 104[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]CC DD[/TD]
[TD]17000[/TD]
[/TR]
</tbody>[/TABLE]
Note: The information starts on Row 2.

 

[István Hirsch]

Thanks for the tables, they are fine. However, I still can not follow the process. For example, in sheet 2 the line before the last says that the Amount (Price?) for 104 CC is 18000, but the last line states that 104 CC is 17000. By the way, does the last line mean that in sectors 103, 104 and 105, rows BB and CC the Amount is the same, 17000 each?

 

[lawi227]

Thanks for the tables, they are fine. However, I still can not follow the process. For example, in sheet 2 the line before the last says that the Amount (Price?) for 104 CC is 18000, but the last line states that 104 CC is 17000. By the way, does the last line mean that in sectors 103, 104 and 105, rows BB and CC the Amount is the same, 17000 each?

The first table says 103 CC is $18,000 and the second table says 104 CC is $17,000.

Sheet 2 may be easiest to understand if you look at column B - for example, "103 & 105 BB" means section 103 (row BB) = $18,500 AND section 105 (row BB) = $18,500.

I need a "search, find, and return" function that matches the section # and row letter and returns the price. I'm not sure how to do that without it looking for an exact match. For example, if I am looking for 103 CC, I need it to find the row that contains "103" AND "CC" and return the amount in Column E. However, you'll notice that row contains 3 section #'s in column C - that is where the big problem is.

Thanks so much for your help!