Converting Alphanumeric text to usable number value

Willingtolearn

New Member
Joined
Jul 18, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I have a need to convert alphanumeric text into number value.

Our system imports alphanumeric text for values from our supply software and I need to convert these text values into numerical values. The system uses alphabetical substitutes for large numbers, i.e. M = Million, K = Thousands
Example:
Part A : 4.326M
Part B : 168.655K
Part C : 79.461K
Part D : 1.21M

What I would like to do is convert these to numerical values in a new column within Excel so that I can work with them.

Part A : 4.326M 4,326,000
Part B : 168.655K 168,655
Part C : 79.461K 79,461
Part D : 1.21M 1,210,000

I have looked through the postings but did not see any that addresses this issue. If I have missed the posting, kindly point me in the correct direction.

Thank you for the help.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,556
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Are those values (for example, this one... Part A : 4.326M) all in a single cell?

Also, is a VBA macro solution acceptable to change the values in place?
Or did you want a formula to put the converted value into an adjacent cell?
 

Willingtolearn

New Member
Joined
Jul 18, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Rick,

Thank you for the response. As per your questions:
1) The value string in my example would be in two cells; Cell one is just "Part A :" Cell two is just 4.326M. I am interested in converting 4.326M.
2)I would like to have a formula that puts the converted value into an adjacent cell

Thank you for your help.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,556
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Assuming your first value is in cell B1, put this formula in cell C1 and copy it down...
Excel Formula:
=LEFT(B1,LEN(B1)-1)*IF(RIGHT(B1)="K",1000,IF(RIGHT(B1)="M",1000000,1))
Note: You will have to Custom Format the cells using #,##0 in order to get the numbers to display with commas.
 
Solution

Forum statistics

Threads
1,148,244
Messages
5,745,606
Members
423,964
Latest member
Rayds

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