Text to columns

[csilabgirl]

Excel 2002

I am pasting some data into a spreadsheet and then using TEXT TO COLUMNS to separate it based on the "=". I have long international phone numbers such as seen below that when I paste them in and use TEXT TO COLUMNS, it convers the phone number to scientific notation, even if I have already set the column format that it will be separating the data into as "TEXT" instead of general. Any ideas on how I can prevent this?

<TABLE style="WIDTH: 197pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=262 x:str><COLGROUP><COL style="WIDTH: 197pt; mso-width-source: userset; mso-width-alt: 9581" width=262><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 197pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=262>Phone #1 = 2358038402118

Because the phone number is never on the same row when I paste the data in, I cannot use formulas like RIGHT() to obtain it, I have to use text to columns. Thank you for the help.
</TD></TR></TBODY></TABLE>

 

[PhilW_34]

Hello,

It is very likely I found this code on this site. I use it all the time but I can not take credit for it. I just create a button in the ribbon and direct it to this code. Do not select entire columns or it will take forever to run. However, if you select a block of numbers and run this code, it should do exactly what you want. I hope this helps.

Phil

Sub ConvertToText()
    
        Application.ScreenUpdating = False
        
        'Convert only cells with data in selected column to Text format.
        
        'Range(ActiveCell.End(xlDown), ActiveCell.End(xlUp)).Select
            With Selection
                .NumberFormat = "@"
            End With
          
        'Cycle through selected cells (Remove Ticks or Trim)
        Application.Calculation = xlCalculationManual  'pre XL97 xlManual
            Dim cell As Range
            Dim ix As Long
            Dim tCells As Long
            tCells = Selection.Count
                For ix = 1 To tCells
                Selection.Item(ix).Formula = Trim(Selection.Item(ix).Formula)
                Next ix
        Application.Calculation = xlCalculationAutomatic  'pre XL97 xlAutomatic
        Application.ScreenUpdating = True
        
    End Sub

 

[PhilW_34]

Hello Again,

I may have misread your request. I'm working in 2007. When I have a row of data and I select Text to Column and enter an = sign as the separator, it seems to work for me. It is giving me the Text to Columns Wizard and allowing me to change the second column created to Text. I'm unable to recreate your dilemma. Sorry if I wasn't much help.

Phil

 

[Jonmo1]

The number is only being DISPLAYED in scientific notation.
The actual number is still there.

You could just change the format of the cell to number AFTER the text to columns..

 

[csilabgirl]

PhilW, thank you for trying to help.

Jonmo - thank you for the suggestion, I can program that into the macro