Sep 20 2021 12:31 PM
this is in the excel cell how it looks (954) 731-5100
see how the number here is actually all the way to the right in the cell (no cell alignment was used)
see there is no ( ) in the formula bar but it is in the cell, causing data problems when I extract them from another database. I can not seem to find a way to make them all the same
number in cell
number in formula bar
this is a exact match in the cell and in the formula bar, this is how I prefer them, this is how the good data comes over from the other database I pull from. How can I get the 1st ones to be like the 2nd ones that match both the formula bar and the excel cell itself?
I'm fixing this in excel and I can not fix as we get the data sent to us like this, so what the move I make to correct the entire column. I wish format painter would do it, but no. Please any suggestions?
Sep 20 2021 12:41 PM
In your examples in red, the cell value is a number. That is why you see it as a plain mumber in the formula bar. A special number format has been applied to it to make it appear as a phone number in the cell, but the value stored in the workbook is the plain number.
If you want a value such as (954) 731-5100 to be the stored value, it must be a text value, not a number.
I don't know why some values come over from the database as a number and others as a text value.
To convert all phone numbers to text values, you could run the following macro. Select the range with phone numbers first.
Sub ConvertPhoneNumbers()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In Selection
If IsNumeric(rng.Value) Then
rng.Value = rng.Text
End If
Next rng
Application.ScreenUpdating = True
End Sub
Sep 20 2021 12:42 PM
First one is number formatted with brackets and dashes. Second one is text. If you prefer texts you may use
=TEXT(A1,"[<=9999999]###-####;(###) ###-####")
to convert
Sep 20 2021 12:46 PM
I prefer numbers, is there a way to format the entire row in to the number and formatting I had?
(954) 563-5567 like this?
Sep 20 2021 12:53 PM
If in place that's by macro similar to one which @Hans Vogelaar suggested, but in opposite direction. If to copy into another place that could be done by formulas, not sure what do you prefer.