Replacing asterisks in Excel 3

[Petzl]

I have a scenario where the customer has a column with people's names in it but it was exported from a legacy system. It has an asterisk at the front of the name so the name appears as *Bob Smith. I know how to get the name with formula's but I wanted an easier way by using the edit replace functionality in Excel. Trouble is if you ask Excel to find * and replace with nothing, it assumes the * is a wildcard and deletes the whole field!

Any way around that???

Thank!

 

[Loomah]

add a tilda to indicate literal character
ie
~*

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SQLBI]

Hi,

You could do this with VBA code as follows;

Public Sub Asterisk()

Sheet1.Select
Cells(2,1).Select 'Cell A2

Do

    If Left(ActiveCell) ,1) = "*" Then

        ActiveCell.Value = Right(ActiveCell, Len(ActiveCell)-1)

        ActiveCell.Offset(1, 0).Select

    Else

        ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell) = True

End Sub

Copy this code to a module and run it, but change the sheet and cell ref to suit your workbook.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[combo]

Ask excel to find ~* and replace by nothing.

combo

 

[Petzl]

That simple!!! Knew there had to be something!

Thanks guys!!