Removing price from string

[Vikas Kumar]

Hi Experts,

Any help would be appreciated in this case to removing price from given sample string,

Product Bought <colgroup><col width="1017"></colgroup><tbody> </tbody> Output Required <colgroup><col width="753"></colgroup><tbody> </tbody> 1 .Basic Listing1850.00 <colgroup><col width="1017"></colgroup><tbody> </tbody> 1 .Basic Listing <colgroup><col width="753"></colgroup><tbody> </tbody> 1 .Platinum Listing11,700.00 2 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier 11600.00 <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing 2 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing11,700.00 2 .Platinum Listing11,700.00 <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing 2 .Platinum Listing <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing11,700.00 2 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier 614,000.00 <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing 2 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing-Group 4115,700.00 <colgroup><col width="1017"></colgroup><tbody> </tbody> 1 .Platinum Listing-Group <colgroup><col width="753"></colgroup><tbody> </tbody> 1 .Platinum Listing11,700.00 2 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier 614,000.00 <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing 2 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing11,700.00 2 .Platinum Listing11,700.00 3 .Platinum Listing11,700.00 4 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier 512,000.00 <colgroup><col></colgroup><tbody> </tbody> 1 .Platinum Listing 2 .Platinum Listing 3 .Platinum Listing 4 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier <colgroup><col></colgroup><tbody> </tbody> <tbody> </tbody>

Hope I am able to explain situation well. Please suggest a way towards solution.

<colgroup><col></colgroup><tbody>
</tbody>

 

[MickG]

Try this for data Starting "A3" down

[COLOR=navy]Sub[/COLOR] MG07Nov55
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, sp [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] s, Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nTxt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    sp = Split(Dn.Value, " ")
    [COLOR=navy]For[/COLOR] n = 0 To UBound(sp)
        [COLOR=navy]If[/COLOR] Len(sp(n)) <= 3 [COLOR=navy]Then[/COLOR]
            nTxt = nTxt & " " & sp(n)
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]For[/COLOR] s = 1 To Len(sp(n))
                [COLOR=navy]If[/COLOR] Not IsNumeric(Mid(sp(n), s, 1)) And Not Mid(sp(n), s, 1) Like [","] [COLOR=navy]Then[/COLOR]
                    Txt = Txt & Mid(sp(n), s, 1)
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] s
        [COLOR=navy]End[/COLOR] If
            nTxt = nTxt & " " & Txt: Txt = ""
     [COLOR=navy]Next[/COLOR] n
Dn.Value = nTxt: nTxt = ""
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Vikas Kumar]

Thank you Mick for your valuable time. But It removed quantity also from last string like below one here I would like to erase only bold numbers:

1 .Platinum Listing11,700.00 2 .Platinum Listing11,700.00 3 .Platinum Listing11,700.00 4 .Pack of 2Featured Listings Validity 2Months|Res Sale Tier 512,000.00

And as an output got this. Where we missed out highlighted number also.

1 .Platinum Listing. 2 .Platinum Listing. 3 .Platinum Listing. 4 .Pack of Featured Listings Validity Months|Res Sale Tier .

Can you analyze this one more time please.

 

[MickG]

This should be better !!!

[COLOR="Navy"]Sub[/COLOR] MG08Nov20
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nTxt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, " ")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        [COLOR="Navy"]If[/COLOR] Len(Sp(n)) <= 3 Or InStr(Sp(n), ".00") = 0 [COLOR="Navy"]Then[/COLOR]
            nTxt = nTxt & " " & Sp(n)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]For[/COLOR] s = 1 To Len(Sp(n))
                [COLOR="Navy"]If[/COLOR] Not IsNumeric(Mid(Sp(n), s, 1)) And Not Mid(Sp(n), s, 1) Like [","] [COLOR="Navy"]Then[/COLOR]
                    Txt = Txt & Mid(Sp(n), s, 1)
                [COLOR="Navy"]End[/COLOR] If
                
            [COLOR="Navy"]Next[/COLOR] s
        [COLOR="Navy"]End[/COLOR] If
            nTxt = nTxt & " " & Txt: Txt = ""
     [COLOR="Navy"]Next[/COLOR] n
Dn.Value = nTxt: nTxt = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Vikas Kumar]

This is Best Mick.....Thanks a bunch!!!