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" wrote:

> 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
>
>

[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 wrote:
>
> 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

[Paul Martin]

Hi Dave

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

Regards

Paul Martin
Melbourne, Australia