Replace characters from cell values

[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

 

[Paul Martin]

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

 

[Rowan]

What about =MID(A1,FIND("-",A1)+2,LEN(A1)-(FIND("-",A1)+1))

Hope this helps
Rowan

 

[Paul Martin]

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

 

[Dave Peterson]

You may want to look at VBA's InStr function. Then you don't have to use the
worksheet.find.

 

[Paul Martin]

Hi Dave

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

Regards

Paul Martin
Melbourne, Australia