Strange Behaviour - Help Needed please

[JamieP89]

Hi all,

I'm facing into a problem that I can't seem to explain nor solve.

I have a column/s of data where I have a long string of numbers i.e. '00092736282637282 (with an apostrophe in front)

When I remove the apostrophe I then get given the following view 9.3223E+18 and when I then try to turn this into a number etc for some reason the number would completely change to a number like 0011221600016860000 (as an example) and all rows below would revert to the same example number provided.

Does anyone know how to remove the apostrophe without loosing the original number?

 

[jolivanes]

You mention "without loosing the original number."
Numbers don't have leading zeros.
You could try

Sub Maybe()
Dim i As Long
Columns(1).ColumnWidth = 21    '<---- Or As Required
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row    '<---- Assumes that data is in Column A starting at first cell
        With Cells(i, 1)
            .Value = Mid(Cells(i, 1), 2, 56) * 1
            .NumberFormat = "0"
        End With
    Next i
End Sub

 

[DanteAmor]

Try:

• Select cells
• Select Data > Text to Columns.
• In the Convert Text to Columns Wizard, select Delimited

• Select Next.
• In Step 2, turn off all.

• Select Next.
• On column data format, Select Text option (important)
• Select the Destination cell, select the next cell to the right (important), for example, if the data starts at cell C2, then select cell D2

• Select Finish.

 

[JamieP89]

Try:

• Select cells
• Select Data > Text to Columns.
• In the Convert Text to Columns Wizard, select Delimited

View attachment 83408

• Select Next.
• In Step 2, turn off all.

View attachment 83409

• Select Next.
• On column data format, Select Text option (important)
• Select the Destination cell, select the next cell to the right (important), for example, if the data starts at cell C2, then select cell D2

View attachment 83410

• Select Finish.

This worked a treat - Are you able to help me understand what this did behind the scene that a simple (change the format of the cell doesn't do) - I use Text to columns all the time but never left delimiters blank and had "Treat consecutive delimiters blank" ticked - Is it because we have utilised the "text" format in the text to columns tool?


****UPDATE*** I spoke to soon - It moved the data along and kept the ' in the new column. If I delete the apostrophe one at a time the cell string remains but if i try to find and replace the ' with nothing I loose the number and it defaults to the same number for all of them and delimiting them does the same...

ergh...

 

[DanteAmor]

"Treat consecutive delimiters blank"

It is because there is no delimiter

I loose the number

So the destination must be text and in a new column.