Extracting Number and Text with variable lengths in a string

[Scottb6776]

Extracting a series that has too many variables.

Col A:
[TABLE="width: 668"]
<TBODY>[TR]
[TD]Sender: </SPAN>SBS R-10034793-001000 Inconsistency between a document field and the prof. seg</SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS P-ALTBBJ1 center N10/6024 is blocked against indirect post</SPAN>[/TD]
[/TR]
[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 extract the bold/underlined series, into column B. The problem with this, is that there are too many variables, number of characters, spaces, hyphens, text/numeric mixed. I cannot figure out a way. I have tried everything I could think of. Any help would be greatly appreciated.

 

[MickG]

Maybe !!!

[COLOR="Navy"]Sub[/COLOR] MG13Feb25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Dn.Offset(, 1).Value = Split(Dn.Value, Chr(160))(1)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[WaterGypsy]

Assuming there are no spaces in the data apart from the one after SBS then this should work:

=MID(A2,SEARCH("SBS",A2),SEARCH(" ",A2,SEARCH("SBS",A2)+5)-SEARCH("SBS",A2))

 

[NeonRedSharpie]

If the sample data is truly consistent with ALL of your data, use this:

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1,C1+1)+1)-FIND(" ",A1,FIND(" ",A1)+1+1)+3)

Note: There are some non "space" characters before and after the underlined/bolded part.

 

[Scottb6776]

Thank you to all!

 

[Scottb6776]

Assuming there are no spaces in the data apart from the one after SBS then this should work:

=MID(A2,SEARCH("SBS",A2),SEARCH(" ",A2,SEARCH("SBS",A2)+5)-SEARCH("SBS",A2))

YOU ARE ALL AWESOME!! The formula up above solved my issue. Thank you so much to all.

 

[Scottb6776]

Turns Out I do have a case in which there are a spaces within the series.


[TABLE="width: 559"]
<TBODY>[TR]
[TD]Sender: SBS R-10034793-001000 Inconsistency between a document field </SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS P-ALTBBJ1 center N10/6024 is blocked against indirect post</SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS C-00128 INS Too Cap - NIKON Laser N00/6199 is blocked</SPAN>[/TD]
[/TR]
[TR]
[TD]Sender: SBS C-40002-1 -2 -4 AIR NIKE Settle Old Type SBS Not Allowed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

between the -2 and the -4, I tried to lengthen the num of char, but that only picked up the AIR NIKE portion.

 

[WaterGypsy]

Are you always going to have the format "space dash text" when you want to extend the range you need? or is there some other rule that will apply? I think it might be possible to allow "space dash" but I'll have to think about how to do it