Replace characters from cell values

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

Paul Martin

Hi all

I would like to remove the number and dash from the cell values as
follows:

1 - Apples
2 - Oranges
3 - Bananas
etc

I can't use the MID function as is as the digit prefix can be more than
1 character. I was thinking of the REPLACE function but it doesn't
seem to accept a wildcard character (eg "* - ").

Any ideas appreciated.

Paul Martin

Melbourne, Australia
 
I should mention that I have achieved the above using the following
code, but was wondering if there is something function that will do it
more simply.

For Each rngCell In rngBrands.Cells
sBrand = Replace(rngCell.Value, " - ", "")
Do
sBrand = Mid(sBrand, 2)
Loop While IsNumeric(Left(sBrand, 1))

lstBrands.AddItem sBrand
Next rngCell

Paul Martin
Melbourne, Australia
 
Hi Rowan

I was after a VBA solution, but I have adapted your suggestion as
follows, and it works fine.

sBrand = Mid(sBrand, WorksheetFunction.Find("-", sBrand) + 2)

Thanks

Paul Martin
Melbourne, Australia
 
You may want to look at VBA's InStr function. Then you don't have to use the
worksheet.find.
 
Hi Dave

Thanks. Yes, I should have thought of Instr. Better solution than
what I used.

Regards

Paul Martin
Melbourne, Australia
 
Back
Top