CODEWITHSUNDEEP
excel-formulagoogle-sheets-formula
Divyanth Jayaraj
Divyanth Jayaraj

Reputation: 960

How do I split a column on google sheets into two columns, one with a symbol and the other with a number?

I have a CSV that's generated from a website which, when imported has a column that looks like this

| INR 2000 |

| USD 300  |

| YEN 350  |

I would like to separate the currency symbol and the number into separate columns so that it looks like this

| INR | 2000 |
| USD | 300  |
| YEN | 350  |

Here, the symbol and the amount are in two separate columns. How do I achieve this? I tried going to Data > Split text to columns and all I get is a small popup asking me to specify a seperator. When I do, the number disappears and only the currency symbol remains. Is there anything that I am missing?

enter image description here

Upvotes: 0

Views: 365

Answers (2)

player0
player0

Reputation: 1

You are able to paste CSV data as is into the spreadsheet and go to Data selecting Split text to columns... and even further specifying the delimiter:

0

0

Also, you can simply use keyboard shortcut combo:

LEFT ALT + D + E

And as already mentioned SPLIT formula:

=SPLIT("l,f,x,a,s,f"; ",")
=SPLIT(A1; ",")

For a range/array it would be:

=INDEX(IFERROR(SPLIT(A1:A; ",")))

0

Upvotes: 0

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

If after currency symbol there's space, you can use:

=split(a1," ")

or if you want to split whole column:

=ArrayFormula(iferror(split(a1:a," ")))

Upvotes: 1

Related Questions