Convert All Numbers Stored as Text

turtlepokerman

New Member
Joined
Jun 18, 2012
Messages
28
Hello World,

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

AB
1Item1Apple
2Item1Weight50
3Item1Height25
4Item2Length10

<tbody>
</tbody>

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!!!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,751
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
I have a table of data that looks similar to this:

AB
1Item1Apple
2Item1Weight50
3Item1Height25
4
Item2Length10

<tbody>
</tbody>

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

New Member
Joined
Jun 18, 2012
Messages
28
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:



A
1Item1Cable2
2Cable1Weight50
3CableSize1/0
4CableLength200

<tbody>
</tbody>


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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:



A
1Item1Cable2
2Cable1Weight50
3CableSize1/0
4CableLength200

<tbody>
</tbody>


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...

Code:
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

New Member
Joined
Jun 18, 2012
Messages
28
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 18, 2012
Messages
28
So the code would have to say something similar to this:

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

Grummet

Board Regular
Joined
Jun 17, 2014
Messages
80
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,762
Messages
5,597,980
Members
414,199
Latest member
AMI_MINT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top