Convert text to number in Google Sheets Excel

0 votes
How can I convert these strings to numbers in google sheets & excel, considering the columns are a mixture of billions and millions? $9B $100M $100M $90B
Oct 15, 2022 in Others by Kithuzzz
• 38,010 points
945 views

1 answer to this question.

0 votes

Try:

=INDEX(IF(REGEXMATCH(A1:A4&""; "M");
 REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000; 
 IF(REGEXMATCH(A1:A4&""; "k");
 REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000;
 IF(REGEXMATCH(A1:A4&""; "B");
 REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000000; A1:A4))))

enter image description here

answered Oct 15, 2022 by narikkadan
• 63,420 points

Related Questions In Others

+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
605 views
0 votes
1 answer

How do I get text in a pivot table in excel or google sheets?

Try: =QUERY(QUERY({Data!A:A, Data!Q:S}, "select Col4,max(Col1) ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
860 views
0 votes
0 answers

MS EXCEL: Convert (Height) Decimal Feet & Inches to Inches Only

Using MS Excel (2010), I would like ...READ MORE

Feb 10, 2022 in Others by Edureka
• 13,670 points
381 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
697 views
0 votes
2 answers

In a Spark DataFrame how can I flatten the struct?

// Collect data from input avro file ...READ MORE

answered Jul 4, 2019 in Apache Spark by Dhara dhruve
5,675 views
0 votes
2 answers

How an object array can be converted to string array in java?

System.arraycopy is the most efficient way, but ...READ MORE

answered Aug 8, 2018 in Java by Sushmita
• 6,910 points
4,720 views
0 votes
2 answers

How does random shuffling of an array

Here is a simple way using an ArrayList: List<Integer> ...READ MORE

answered Nov 2, 2018 in Java by Sushmita
• 6,910 points
640 views
0 votes
1 answer

Numpy: Multiplying large arrays with dtype=int8 is SLOW

Unfortunately, as correctly underlined in the comments, the ...READ MORE

answered Sep 5, 2018 in Python by Priyaj
• 58,090 points
2,567 views
0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,420 points
12,065 views
0 votes
1 answer

Convert text date/time to a real date time in excel

For a date conversion: =DATEVALUE(TEXT(A1,"MM/DD/YYYY")) For a time conversion: =TIMEVALUE(TEXT(A1,"HH:MM:SS")) For ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
306 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP