Share via

LEN formula needing sum ability

Anonymous
2024-05-08T18:23:28+00:00

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!

Microsoft 365 and Office | Excel | For business | MacOS

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.

0 comments

Answer accepted by question author

Anonymous
2024-05-09T01:31:23+00:00

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

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-05-08T19:22:31+00:00

    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," ")

    Was this answer helpful?

    0 comments