Divide entire column in excel (accounting) in 2 separate column (symbol + value)

Question

how to divide excel field with format accounting omitted, for example: $ 220 € 140 $ 220 € 140 $ 220 € 140 $ 220 € 140 etc...etc... So, from one field-column A, to get 2 column A (for accounting symbol) and B (for value)?

Answer 1

You can do it like this:

First column:

Second column:

The first extracts a char from the beggining, and the second removes the first two displaying 100 chars ahead. You can convert the second column to number by wrapping the formula in a value().

Update:

If you know that will be a space between the two you can use this formulas instead and parse a more than one char accounting symbol:

=LEFT(A1;FIND(" ";A1))

=MID(A1;FIND(" ";A1)+1;100)