This content is likely not relevant anymore. Try searching or browse recent questions.
How to combine two maybe three functions to make doc compact
I have a sheets document that takes amounts like 160M splits 160 from 'M' and checks if the unit is a k, m, or b then multiplies by that amount (ex. m = cell*1000000). I want to know a way to make the whole process as compact as possible, here is how it looks currently:

Column J has: =LOWER(ArrayFormula(IF(F2:F="",,REGEXREPLACE(F2:F,"([0-9\.]+)",",\$1,"))))
Column K has: =SPLIT(J2,",")
Column M has: =IFS(EXACT(L2,O2),K2*1000,EXACT(L2,O3),K2*1000000,EXACT(L2,O4),K2*1000000000)

The formula on column J was copied and all I did was add LOWER to make sure it wasn't case sensitive but I don't really understand it enough to be able to change it further.
Details
Pinned
Locked
All Replies (3)
Hi, Robert G 8404,

For me, or other forum contributors to help (in case I'm not around), I suggest that you share an editable COPY of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do!  Here are some tips to help you get a great answer quickly... Product Experts TIPS FOR SHARING

***** Tip: Read the Tips post above, then check your sharing settings before posting - you want "Anyone with the link can Edit"

Ben Liebrand
Hi Robert,

As Ben has suggested, providing a shared sample sheet makes it much, much easier to propose solutions.  In this case, we really want to see exactly what you have in column F, since I believe that columns J, K, and L could all be eliminated, to simplify your sheet.

On the assumption that column F has text values like 160M, 9k, 999K, 123, and possibly even 1200M, the following formula, placed in cell N2 (or M2), might do what you want.  It needs to be dragged down, but an array formula version is very simple as well.

`=  VALUE( REGEXEXTRACT(LOWER(F2),"([\d.]+)")) `
` * CHOOSE(MATCH(`
`             REGEXEXTRACT(LOWER(F2),"[\d.]+([a-z]?)"),`
`             {"b","m","k",""},0),`
`     1000000000,1000000,1000,1)`

Let me know if that helps.
Cheers,
Gill

Hi Robert,

Circling back through some open discussions - has your question here been answered? Do you need more help?

• Leave your demo sheet shared as part of this solution's archive,
• Click Recommend on the post that best addressed your question, and
• Post again soon!

Best,
Gill
This question is locked and replying has been disabled.
10 characters required
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

This reply is no longer available.
Search
Clear search
Close search