This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a data export that spits out a total column with the format: 44444 USD; numerical amount, space, currency.
I am trying to avoid having to text to column on every single export so I am using the LEN Right and LEN left to split them out into two columns of just 4444 and USD, but I cannot get Excel to recognize the first as being numerical and thus giving me a sum total in a pivot table. Any advice? Thanks in advance!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
=--TEXTBEFORE(A2," ")
You can use the double hyphen (--) to convert text to number. To do this, simply enter the double hyphen before your formula result which is text.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(64, 92%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERM%3C/text%3E%3C/svg%3E)
Hi Jeremy. I am an Excel user like you.
I don't work on a Mac, but these formulas should be common for both platforms.
To pull out the amount and format it as a number to be summed use this formula with a space in the quotes as the delimiter.
=VALUE(TEXTBEFORE(A2," "))
To separate the currency use this formula.
=TEXTAFTER(A2," ")