Column Format - Delimiter/Text to Columns Issue

[ColdplayCoaster]

Hello,

I have a roster with two columns of data; Column A is formatted to a number with thousand separator on and no decimal. Column C is formatted (supposedly) the same, but does not display the thousand separator.

I suspect it's a formatting issue, but I've been unsuccessful in Text to Columns formatting, or other methods. Any tips to get both columns to read the same (for Index/Match purposes)?

There's 26,000 rows, so manually typing them in isn't really an option for me.

Thanks!

 

[jamiemarie]

When you right click and go to Format Cells, does the Number tab look like the picture below?

 

[Fluff]

At a guess, either one, or both, columns are text not numbers.
Select both the columns & set the format to Number & to show 2 decimals
Does either column appear differently?

 

[ColdplayCoaster]

Yes to both. When I click the entire column, it shows exactly as you posted.

The interesting thing, is I can type over the data in C and get the result I need. 1261 -> typed in will give me 1,261, etc. The formulas will work accordingly then up to that point, but not past it.

And dragging it down to auto-fill the numbers won't work because Column C is a unique seniority # that occasionally skips; Column A is the record count and therefore is directly sequential.

 

[ColdplayCoaster]

Would posting the spreadsheet assist in helping me solve?

 

[Fluff]

Can you answer my question?

 

[ColdplayCoaster]

Can you answer my question?

Yes, Column A displays as 1,747.00, 1,748.00, etc. (correct) while Column C displays as 2004, 2005, 2006, etc...

 

[overclock]

I had a similar situation with six digit ID numbers. One system would have leading zeroes while others wouldn't. So I cobbled the code below together from a couple of sources. Select the cells you want to convert and run the macro. This is easily converted to do a define column if the reference doesn't change.

Sub ConvertSelectionFormatting()
'Converts selction to six digits

Dim rng As Range
Dim WorkRng As Range

On Error Resume Next
Set WorkRng = Application.Selection

OptimizeCode_Begin

For Each rng In WorkRng
With rng
.NumberFormat = "@"
.Value = Format(.Value, "000000")
End With
Next

OptimizeCode_End

End Sub

The macro takes a while due to Excel updating the formatting and then redrawing the new format on the screen. Thus the OptimizeCode_Begin and OptimizeCode_End macro references. Put them in a different module so you can reference them in all of your macros. Here's the code for those:

Sub OptimizeCode_Begin()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
      
End Sub

Sub OptimizeCode_End()

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.CutCopyMode = False
  
End Sub

 

[Fluff]

Column C displays as 2004, 2005, 2006, etc...

This suggests that col C is text & not numbers, you will need to convert them to numbers.