Convert All Numbers Stored as Text

[turtlepokerman]

Hello World,

I have a table of data that looks similar to this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item1[/TD]
[TD="align: center"]Apple[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Item1Weight[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Item1Height[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Item2Length[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]

This structure repeats about 500x. I am importing data currently and the green arrows apprear in the corners and say numbers stored as text for rows B2:B4. How do I select all of the data in column B and have it automatically convert all of the numbers stored as text into numbers while leaving the text items alone? I am willing to work in VBA, since that I already have some functions created in it.

All help is appreciated!!!

 

[Michael M]

got to a seperate cell and put in the number 1
Now copy that cell, highlight the numbers in column "B"
Right click in that column, Select Paste Special
Halfway down the dialog box that pops up, check the Multiply Button
Click OK
They should all be numbers now !

 

[Rick Rothstein]

I have a table of data that looks similar to this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item1[/TD]
[TD="align: center"]Apple[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Item1Weight[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Item1Height[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Item2Length[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]

This structure repeats about 500x. I am importing data currently and the green arrows apprear in the corners and say numbers stored as text for rows B2:B4. How do I select all of the data in column B and have it automatically convert all of the numbers stored as text into numbers while leaving the text items alone? I am willing to work in VBA, since that I already have some functions created in it.

Try this... select all of Column B, call up the "Text To Columns" dialog box (Data tab/Data Tools group/Text to Columns button) and as soon as the dialog box appears, click the Finish button.

 

[turtlepokerman]

Okay there is one other problem I am having. There are some numbers stored as text that excel thinks is a "Text Date with 2-Digit Year". I want to keep those values as a text value and not to have excel convert them to a date. Example Below:



[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item1[/TD]
[TD="align: center"]Cable2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Cable1Weight[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]CableSize[/TD]
[TD="align: center"]1/0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]CableLength[/TD]
[TD="align: center"]200[/TD]
[/TR]
</tbody>[/TABLE]


How do I keep excel from changing the 1/0 to a date and leave it as text, but still convert "200" from text to a number?

Thanks for the responses so far!

 

[Rick Rothstein]

Okay there is one other problem I am having. There are some numbers stored as text that excel thinks is a "Text Date with 2-Digit Year". I want to keep those values as a text value and not to have excel convert them to a date. Example Below:



[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item1[/TD]
[TD="align: center"]Cable2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Cable1Weight[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]CableSize[/TD]
[TD="align: center"]1/0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]CableLength[/TD]
[TD="align: center"]200[/TD]
[/TR]
</tbody>[/TABLE]


How do I keep excel from changing the 1/0 to a date and leave it as text, but still convert "200" from text to a number?

Give this macro a try...

Sub ConvertTextNumbersToRealNumbers()
  Dim Addr As String
  Addr = "B1:B" & Cells(Rows.Count, "B").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("If(ISNUMBER(0+SUBSTITUTE(@,""/"",""X"")),0+@,@)", "@", Addr))
  Range(Addr).NumberFormat = "General"
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ConvertTextNumbersToRealNumbers) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[turtlepokerman]

I gave the macro a try but it converted the text "1/0" to 36526. I would like to keep it as "1/0". But it did convert the numbers stored as text to numbers. So we made some progress!!

Thanks for the attempt.

 

[Rick Rothstein]

I gave the macro a try but it converted the text "1/0" to 36526. I would like to keep it as "1/0". But it did convert the numbers stored as text to numbers. So we made some progress!!

I assumed all your cells were formatted as TEXT... with that assumption, the code I posted worked fine. Given that it did not work correctly for you, can you select the cell with 1/0 in it and tell me what shows in the Formula Bar and then call up the Cell Formatting dialog box (right click the cell and click "Format Cells..."), select Custom from the left-hand list and tell me what shows in the Type field?

 

[turtlepokerman]

Formula bar show: 1/0

Cell Format Says:
Category: Custom
Type: General

 

[turtlepokerman]

So the code would have to say something similar to this:

EVALUATE(IF(ISERROR(FIND("/",B2,1)),Addr.NumberFormat = "General","Do Nothing"))

 

[Grummet]

Do you have error checking on? (It adds a tiny green triangle in the top left of the cell when there's an error).
In my experience any time there are numbers stored as text Excel will suspect that it's not correct. When you select a cell with this error tag a dropdown is made available which has an option to "Convert to Number".

If it is available to you then you can do all the cells at once. Select the first one, press CTRL + SHIFT + DOWN to select the whole set of bad numbers, scroll back up to the top and the dropdown box is there.



The other, just as easy option, is as follows:
1. Select the range of bad cells
2. Run the Text to Column wizard
3. (Step 1 of 3) Choose Delimited - Hit Next
4. (Step 2 of 3) Uncheck all of the check boxes - Hit Next
5. (Step 3 of 3) Choose the General format - Hit Finish

They should now be in the Number format.



If neither of these options work then you may have hidden characters in the cells either before or after the number.

If the hidden characters are just simple s p a c e s then you can fix them with this formula. =TRIM(A1)
If they are still not changing into numbers try this:
1. Highlight the cells
2. Open Find & Replace
3. In the Replace tab -- Find what: Enter the key combination ALT + 255 (you must use the keypad for this) This should enter in a character which LOOKS like a normal space, but it isn't, so it can't be removed with the TRIM or CLEAN formulas.
4. Leave Replace With: blank
5. Ensure the Within: drop-down is set to Sheet (and therefore only your selected cells)
6. Hit Replace all and you should be good to go.


Good luck!

-Grummet