Remove # from a text

mohsandeep

New Member
Joined
Mar 18, 2012
Messages
35
Hello,

Could you please help me remove "#" values from a text below:

"COUNTRIES W/O #" are the result I am expecting. Kindly suggest a formula to remove the ";#" values and the numbers and a ", " when there are two or more Countries.

Countries with #
Countries W/O #
Finland;#73
Finland
Italy;#113
Italy
Austria;#14;#Germany;#60;#United Kingdom;#80;#Netherlands;#169
Austria, Germany, United Kingdom, Netherlands
Germany;#60;#United Kingdom;#80;#Netherlands;#169;#Austria;#14
Germany, United Kingdom, Netherlands, Austria
Netherlands;#169
Netherlands

<TBODY>
</TBODY>
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
mohsandeep,

I only became aware of the existence of 'regular expressions' a couple of days back. It was as a result of a response by Firefly2012 to a thread that I had contributed to.
Firfly2012 would appear to be a bit of a whizz with regular expressions.

LINK IF CURIOUS

This is the first time I have attempted to use RegExp but I think it will do what you are asking.

You will need to go to the VB editor, click Tools > References > locate and Tick 'Microsoft VBScript Regular Expressions 5.5' > ok

Then in a code module, enter the following function.....

Code:
Public Function NoHash(MyString As String)
Dim reg As New RegExp
reg.Global = True
reg.Ignorecase = True
reg.MultiLine = False
reg.Pattern = ";#[1234567890;]{1,99}"
If reg.Test(MyString) Then
MyString = reg.Replace(MyString, "")
End If
reg.Pattern = "#"
If reg.Test(MyString) Then
MyString = reg.Replace(MyString, ", ")
End If
NoHash = MyString
End Function

Then use the function in your sheet....


Excel 2007
AB
1Countries with #Countries W/O #
2Finland;#73Finland
3Italy;#113Italy
4Austria;#14;#Germany;#60;#United Kingdom;#80;#Netherlands;#169Austria, Germany, United Kingdom, Netherlands
5Germany;#60;#United Kingdom;#80;#NetherlandsGermany, United Kingdom, Netherlands
6Netherlands;#169Netherlands

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
B2=Nohash(A2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Hope that helps.
 
Last edited:
Upvote 0
I only became aware of the existence of 'regular expressions' a couple of days back. It was as a result of a response by Firefly2012 to a thread that I had contributed to.
Firfly2012 would appear to be a bit of a whizz with regular expressions.

LINK IF CURIOUS

This is the first time I have attempted to use RegExp but I think it will do what you are asking.
It can also be done without using Regular Expressions...
Code:
Sub NoHashMarks()
  Dim LastRow As Long
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Cells(StartRow, "B").Resize(LastRow - StartRow + 1)
    .Value = Cells(StartRow, "A").Resize(LastRow - StartRow + 1).Value
    .Replace ";#*;#", ", ", xlPart
    .Replace ";#*", "", xlPart
  End With
End Sub
 
Upvote 0
Thank you so much, it works like a charms. This really solved my problem. :)

Thank you so much for all your help! Thank you Snakeship, Thank you Rick.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top