Get rid of £

[LisaGreen]

Has anyone had rouble with this at all?

Lisa

[HansV]

Do you mean ruble?

Please provide more details.

[LisaGreen]

Hi Hans.. beste wensen!

No. I mean the pound sign. =alt 0163.

I tried to put an image here and don't know how to do that apoligies. trying to get something together to to send.
The "new" snipping tool isn't helping eithee

aapologies onc
e more.
Lisa

[HansV]

An image is just an attachment; you can add it in the Full Editor.

A £ sign can be part of text values. You can remove it as follows:
- Select the range.
- Press Ctrl+H to activate the Replace dialog.
- Enter £ in the 'Find what' box and leave the 'Replace with' box empty.
- If you don't see the search options, click 'Options >>'.
- Make sure that the 'Match entire cell contents' check box is not ticked.
- Click 'Replace All'.

For number values, the £ sign can be part of the number format. You cannot remove those using the Replace dialog. Instead, select the cells, and set the number format to General or to Number.

[snb]

In my universe (codepage 437 or 850) I found the pound at alt-156. (you might try GWK )

[LisaGreen]

Hans . here is a screenshot of whay'ts happening.
The string also contained a 0160-nbsp. I got rid of that with sustitute.
IMy eventual aim is to extrcact the two nubers and multiply.
Still can't sseem to get the snippet inline. Awwww.

Lisa.

[HansV]

See Tip: adding an attachment

Could you attach a small sample workbook (without sensitive data) with some of these £ cells in it?

[snb]

Enter C3
In the formula bar: select the Pound symbol / copy
In the replace window: paste in the 'find what' box
'press' Replace all.

[LisaGreen]

Hey Hans Did you get the xlsm file please?

Lisa

[LisaGreen]

@SNB.. didn't work,

Lisa

[HansV]

Hey Hans Did you get the xlsm file please?

Nope.

[LisaGreen]

Anoer try.

[HansV]

Replace works for me:

S2486.png

Make sure that the check "Match entire cell contents" is not ticked.

[LisaGreen]

Hy are the numbers all red? The original column was copied directly from a web page,

Lisa.

[HansV]

I assume that the values on the web page were red too...

[rory]

The Find/Replace applies the red font to the rest of the cell too.

[LisaGreen]

Not done with this.

Please try replace. £ to @

TIA
Lisa

[HansV]

In-place:

S2494.png

Result:

S2495.png

As before, Replace makes the formatting of the cells uniform.

[HansV]

A macro to restore the formatting:

Code: Select all

Sub Replace£()
    Dim rng As Range
    Dim cel As Range
    Dim pos As Long
    Application.ScreenUpdating = False
    Set rng = Range(Range("C6"), Range("C6").End(xlDown))
    rng.Replace What:="£", Replacement:="@", LookAt:=xlPart
    For Each cel In rng
        pos = InStr(cel.Value, Chr(160))
        cel.Characters(Start:=pos + 1).Font.Color = vbWhite
    Next cel
    Application.ScreenUpdating = True
End Sub