Pulling Various Letter/Number Combinations from a Cell

[cjvenables]

Hey guys,

I was wondering how I could remove certain parts of text and $ from a cell?

See example below:

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=368 border=0><COLGROUP><COL style="WIDTH: 276pt; mso-width-source: userset; mso-width-alt: 15701" width=368><TBODY><TR style="HEIGHT: 75pt" height=100><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 276pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 75pt; BACKGROUND-COLOR: transparent" width=368 height=100>SS0124822301000-0001 1 DOZEN(s) ordered at .93 billed at 1.00, SS0124822301002-0001 1 DOZEN(s) ordered at .93 billed at 1.00, SS1306251 1 DOZEN(s) ordered at 7.05 billed at 5.25, SS1306021 1 PACK(s) ordered at 3.02 billed at 3.16, SS1309234</TD></TR></TBODY></TABLE>

I need a formula to take out SS and all of the numbers after it and put it into a new cell (all 5 instances). Also, is there a way to take out the number values (.93, 3.02) into a separate cell.

The example is in Column M. Would like to have each SS value into an individual cell after column M.

I probably did a horrible job of explaining. LEFT, MID, RIGHT don't seem to apply in this instance unfortunately, since there are multiple strings of text in 1 cell, but they are not always in the same place.

Thanks for your help!

 

[Gerald Higgins]

Can you be absolutely clear please about what you want the results to be ?

Do you want to lose bits like "DOZEN(S)" and "billed" and so on ?

 

[cjvenables]

I would like all instances of "SS1243456789" in a cell (there could be 6-12 digits after SS). It is not the same for all cells. If those could be put 1 instance per cell next to the cell with all the text, or at worst, all in the same cell.

I gave a bad example. Based on the following example, i would like "is (amount) vs. (amount)"

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=368 border=0><COLGROUP><COL style="WIDTH: 276pt; mso-width-source: userset; mso-width-alt: 15701" width=368><TBODY><TR style="HEIGHT: 45pt" height=60><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 276pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 45pt; BACKGROUND-COLOR: transparent" width=368 height=60>PO price for SS1310062 is $8.27 vs $9.64 ea for 1 dz; PO price for SS1377855 is $18.71 vs $19.07 ea for 3 ct</TD></TR></TBODY></TABLE>
Hope that clears everything up?

 

[Gerald Higgins]

Hope that clears everything up?

Not for me, sorry, I'm still confused.

Can you give us one single example of the INPUT data and also the OUTPUT data. Don't worry about colour coding it.

Just something like this
Input data ABC 123
Output data 123

or whatever....

 

[Peter_SSs]

I'm also not sure exactly what is required.

1. Can you confirm that each of your example strings are contained in a single cell like mine below?

2. If the data is like my screen shot, can you say exactly what should be in
N2:
O2:
P2:
etc until the last result in row 2 and also

N3:
O3:
P3:
etc for row3?

3. If it isn't feasible to do via standard formulas, would a macro be acceptable?

4. What Excel version are you using?

Excel Workbook
	M	N	O	P	Q
1
2	SS0124822301000-0001 1 DOZEN(s) ordered at .93 billed at 1.00, SS0124822301002-0001 1 DOZEN(s) ordered at .93 billed at 1.00, SS1306251 1 DOZEN(s) ordered at 7.05 billed at 5.25, SS1306021 1 PACK(s) ordered at 3.02 billed at 3.16, SS1309234
3	PO price for SS1310062 is $8.27 vs $9.64 ea for 1 dz; PO price for SS1377855 is $18.71 vs $19.07 ea for 3 ct
cjvenables

 

[cjvenables]

Peter_SSs,

Your data is exactly correct.

For cell M2. I would like to extract any instance of the data SS and all of the numbers that follow. Is it possible to list all results individually in N2, O2, P2 (assuming there are 2 results)?

For cell M3. I would like to extract the SS1310062 and the phrase is $8.27 vs $9.64.

<TABLE style="WIDTH: 554pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=738 border=0><COLGROUP><COL style="WIDTH: 221pt; mso-width-source: userset; mso-width-alt: 12544" width=294><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 6613" width=155><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 7125" width=167><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2602" span=2 width=61><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 221pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=294 height=20></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 116pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=155></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 125pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=167></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=61></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=61></TD></TR><TR style="HEIGHT: 91.5pt" height=122><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 221pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 91.5pt; BACKGROUND-COLOR: transparent" width=294 height=122>SS0124822301000-0001 1 DOZEN(s) ordered at .93 billed at 1.00, SS0124822301002-0001 1 DOZEN(s) ordered at .93 billed at 1.00, SS1306251 1 DOZEN(s) ordered at 7.05 billed at 5.25, SS1306021 1 PACK(s) ordered at 3.02 billed at 3.16
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SS0124822301000-0001</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SS0124822301002-0001</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SS1306251</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SS1306021</TD></TR><TR style="HEIGHT: 64.5pt; mso-height-source: userset" height=86><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 221pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 64.5pt; BACKGROUND-COLOR: transparent" width=294 height=86>PO price for SS1310062 is $8.27 vs $9.64 ea for 1 dz; PO price for SS1377855 is $18.71 vs $19.07 ea for 3 ct
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SS1310062 is $8.27 vs $9.64</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SS1377855 is $18.71 vs $19.07</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Based on your table below, I would like the values returned to be in the columns next to the text. I cannot download the Excel genie to this computer, so imagine the purple text is in N2, O2, P2, Q2 and N3, O3.

Thanks for your help!

 

[Peter_SSs]

Is it possible to list all results individually in N2, O2, P2 (assuming there are 2 results)?

How do we get 2 individually listed results into 3 cells?

imagine the purple text is in N2, O2, P2, Q2

This appears to be 4 cells for row 2, but from what I can see, there appears to be only 3 groups of purple cells in row 3 of your table.

I cannot download the Excel genie to this computer

The third suggestion in my signature block regarding screen shots does not require any download.

Also, if you listed exactly what should be in each cell in the format I suggested, at least we would be sure. That is ..

N2:
O2:
P2:
etc