Variable length numeric extraction from a String

[dfuser]

I have the following data set that I need to be able to extract the numeric values after a hyphen...

nnnn-IF8 (aa); return 8
nnnn-OF8 (aa); return 8
nnnn-IB16 (aa); return 16
nnnn-OB32 (aa); return 32
nnnn-IR6I (aa); return 6
nnnn-IT6I (aa); return 6

I'm looking for a single formula... any help is greatly appreciated! Thank you.

 

[Rick Rothstein]

Thank you, I'm not having a lot of luck with this... I'm getting a #N/A result.

Let's try my being more specific and see if it's me (which is likely)...

I have 6 cells AL4...AL9 containing the following...

1756-IF8 (AI)
1756-OF4 (AO)
1756-IB16 (DI)
1756-OB32 (DO)
1756-IR6I (RTD)
1756-IT6I (TC)

I'd like to extract for use in calculations the 8, 4 (typo from the first round), 16, 32, 6, and 6 into AM4....AM9

Does that help to clarify my request? Thank you.

Your request was clear to me from the beginning... and the formula I posted works fine for the first data item being in A1... this is the first time you mentioned the data starts in AL4, so the cell references in my formula have to be adjusted for that location.

=LOOKUP(9.9E+307,--LEFT(MID(AL4,FIND("-",AL4)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(AL4,FIND("-",AL4),99)&3^45))-1,99),ROW($1:$99)))

 

[dfuser]

Your request was clear to me from the beginning... and the formula I posted works fine for the first data item being in A1... this is the first time you mentioned the data starts in AL4, so the cell references in my formula have to be adjusted for that location.

=LOOKUP(9.9E+307,--LEFT(MID(AL4,FIND("-",AL4)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(AL4,FIND("-",AL4),99)&3^45))-1,99),ROW($1:$99)))

Rick, as I said... it was likely me; thank you, this works perfectly! I appreciate the help!

 

[Scottb6776]

I have a similar issue, perhaps you can help me. You seem to have extensive knowledge on these formulas.

[TABLE="width: 891"]
<TBODY>[TR]
[TD] Sender: SBS R-10034793-001000 Inconsistency between a document field and the prof. seg
</SPAN>[TABLE="width: 891"]
<TBODY>[TR]
[TD] Sender: SBS P-ALTBBJ1 center N10/6024 is blocked against indirect post</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[TABLE="width: 891"]
<TBODY>[TR]
[TD] Sender: SBS C-00128 INS Too Cap - NIKON Laser N00/6199 is blocked</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


What I am needing to do: is be able to just extract the underlined series. I tried to extract everything after the ":" and end at the text, but as you can see, there are texts in my series. I have been pulling my hair with this for days. Any help would be appreciated. Thank you so much.

 

[Rick Rothstein]

I have a similar issue, perhaps you can help me. You seem to have extensive knowledge on these formulas.

[TABLE="width: 891"]
<tbody>[TR]
[TD]
Sender: SBS R-10034793-001000 Inconsistency between a document field and the prof. seg
[TABLE="width: 891"]
<tbody>[TR]
[TD] Sender: SBS P-ALTBBJ1 center N10/6024 is blocked against indirect post
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 891"]
<tbody>[TR]
[TD] Sender: SBS C-00128 INS Too Cap - NIKON Laser N00/6199 is blocked
[/TD]
[/TR]
</tbody>[/TABLE]


What I am needing to do: is be able to just extract the underlined series. I tried to extract everything after the ":" and end at the text, but as you can see, there are texts in my series. I have been pulling my hair with this for days. Any help would be appreciated. Thank you so much.

A couple of questions about the "shape" of your data...

1) Is the text you want always located after the word "Sender:" followed by a single space?

2) Does text that you want always contain a single space after the third character (SBS in your examples) and no other spaces elsewhere?

 

[Scottb6776]

"Sender: " Will always be there.

[TABLE="width: 889"]
<TBODY>[TR]
[TD]1. Sender: SBS C-40002-1 -5 -1 AUTOBODY POLISHING Settlement Receiver Type SBS Not Allowed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

2. In this case, the series has a space following -1 and -5.

I apologize for the multi post, Very new to forums.

 

[Rick Rothstein]

[TABLE="width: 889"]
<tbody>[TR]
[TD]1. Sender: SBS C-40002-1 -5 -1 AUTOBODY POLISHING Settlement Receiver Type SBS Not Allowed
[/TD]
[/TR]
</tbody>[/TABLE]

2. In this case, the series has a space following -1 and -5.

Will the spaces, if there are embedded spaces, always be in front of a dash?

 

[Scottb6776]

Will the spaces, if there are embedded spaces, always be in front of a dash?

Yes, if there are any spaces it will be before the "-"

 

[Rick Rothstein]

Yes, if there are any spaces it will be before the "-"

Okay, assuming your first text string is in cell A1, give this formula a try...

=MID(A1,SEARCH("Sender: ",A1)+8,FIND(" ",SUBSTITUTE(A1," -","--"),13)-SEARCH("Sender: ",A1)-8)

 

[Scottb6776]

Okay, assuming your first text string is in cell A1, give this formula a try...

=MID(A1,SEARCH("Sender: ",A1)+8,FIND(" ",SUBSTITUTE(A1," -","--"),13)-SEARCH("Sender: ",A1)-8)

You are a genius. Thank you Mr. Rick R.