Excel stop converting text to number automatically when find and replace

[learning_grexcel]

The data I copied from web has non-breaking space on all the columns.

When I find and replace it in the workbook, it changes all the text values to numbers.


I have a column with values "00001", "00002", it replaces all of them to 1, 2. Is there anyway I can fix this?

 

[mole999]

are you using formula or code, what are the parameters

 

[Rick Rothstein]

The data I copied from web has non-breaking space on all the columns.

When I find and replace it in the workbook, it changes all the text values to numbers.

Are you doing the find and replace in a macro?

 

[learning_grexcel]

Are you doing the find and replace in a macro?

I'm replacing manually using "find and replace dialog box".

 

[Rick Rothstein]

I'm replacing manually using "find and replace dialog box".

Assuming for example purposes that your values are in Columns G:J starting on Row 1, put this formula in an unused column...

=SUBSTITUTE(G1,CHAR(160),"")

and copy it across so that all your data columns are covered by the copied across formula, then copy all of those formula cells in Row 1 down to the last row with values in Column G. Now, select all of the those formula cells and copy them, then paste their values only over top of the formulas. Now, Cut all of those values and paste them over top of the original data. Your leading zeroes should remain displayed now.

 

[SpillerBD]

Substitue results as a Text string.
If you begin with the cells as Text instead of General, a simple macro still rids the sheet of those pesky Char(0160) and you still have text, or leading zeroes.

We use the following.

Sub Code_160()
'
' Code_160 Macro
'Removes all Characters of Code 0160 from Active WorkSheet
'This Character Code is often included in copied or downloaded
'spreadsheet data that interferes with Excel evaluations.
'Add to the QAT with suitable Icon. Suggestion is the "Unhappy" Face
    Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub