replacing "-"

  • Thread starter Thread starter snax500
  • Start date Start date
S

snax500

In Excel2000, when I replace the "-" in the following text (it's an
accounting code):

46-55-70-00-16-95-100-0000-8901

I get:

4.6557E+22

I have the cells formatted as text.
I want it to look like this:

46557000169510000008901

Thanks
 
Interesting Excel feature. It ignores the Text cell format. If no one
comes up with a better idea you can use a macro to overcome this. This one
assumes the cell has the Text format as you said:

Sub ReplaceDashes()
Dim Cell As Range
Set Cell = Cells.Find("-", , xlValues)
GoTo StartHere
Do
Cell.Value = Replace(Cell.Value, "-", "")
Set Cell = Cells.FindNext(Cell)
StartHere:
Loop While Not Cell Is Nothing
End Sub
 
Might be another, easier way, but you can try this:

Enter an apostrophe (') into an empty cell.
Right click and choose "Copy",
Select the cells containing your account codes,
Right click in the selection and choose "PasteSpecial",
Click "Add", then <OK>.

While these cells are still selected, do your regular "replace",
<Edit> <Replace>
Enter the "dash" into "FindWhat",
Leave "ReplaceWith" blank,
And click on <ReplaceAll>

Be careful not to "lose" your original cell where you entered the
apostrophe.
After you type it and hit <Enter>, there is *nothing* visible in the cell
itself, and it's even difficult to see in the formula bar, when you go back
to look for it.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


In Excel2000, when I replace the "-" in the following text (it's an
accounting code):

46-55-70-00-16-95-100-0000-8901

I get:

4.6557E+22

I have the cells formatted as text.
I want it to look like this:

46557000169510000008901

Thanks
 
snax500
You could use this formula in a helper column, and then Copy and Paste_Special>Values over the orginal

=SUBSTITUTE(A1,"-",""

This will produce a text result so excel won't convert it to scientific notation

Good Luck
Mark Graesse
(e-mail address removed)

----- snax500 wrote: ----

In Excel2000, when I replace the "-" in the following text (it's a
accounting code)

46-55-70-00-16-95-100-0000-890

I get

4.6557E+2

I have the cells formatted as text
I want it to look like this

4655700016951000000890

Thank
 
something like :-
=CONCATENATE(LEFT(A1,2),MID(A1,4,2),MID(A1,7,2),MID(A1,10,2),MID(A1,13,2),MID(A1,16,2),MID(A1,19,3),MID(A1,23,4),MID(A1,28,4)
 
Back
Top