How parse Numbers cell into two cells
Using Numbers on Mac, I imported CSV file from Quicken. One column contains both number of shares and price/share data. How can I parse those cells/columns into separate cells/columns?
iMac 24″, macOS 13.2
Using Numbers on Mac, I imported CSV file from Quicken. One column contains both number of shares and price/share data. How can I parse those cells/columns into separate cells/columns?
iMac 24″, macOS 13.2
You can add two adjacent columns, then use TEXTBEFORE and TEXTAFTER something like this:
In C1, filled down:
=TEXTBEFORE(B2," ")*1
In D1, filled down:
=TEXTAFTER(B2," ")*1
Use ; in the formulas instead of , if your region uses , as the decimal separator.
This assumes there is a space separating the numbers of share and price. If it's something else then put that between the " " in the formulas instead.
The *1 coerces the text to a number.
You can then "remove" the formulas in the added columns by selecting them, command-c to copy, Edit > Paste Formula Results. Then delete the original column if you want.
SG
SG
You can add two adjacent columns, then use TEXTBEFORE and TEXTAFTER something like this:
In C1, filled down:
=TEXTBEFORE(B2," ")*1
In D1, filled down:
=TEXTAFTER(B2," ")*1
Use ; in the formulas instead of , if your region uses , as the decimal separator.
This assumes there is a space separating the numbers of share and price. If it's something else then put that between the " " in the formulas instead.
The *1 coerces the text to a number.
You can then "remove" the formulas in the added columns by selecting them, command-c to copy, Edit > Paste Formula Results. Then delete the original column if you want.
SG
SG
Thanks so much for your response. This is my attempt at following your solution but I keep getting a syntax error.
During one attempt the error message says "range can't be used as a single value."
Help please.
Den
You could use TEXTBEFORE and TEXTAFTER for the value in cell G7 like this:
In column L on row 7:
=TEXTBEFORE(G7," shares")*1
You should insert the blank character before shares!
In column M on row 7:
=TEXTAFTER(G7,"@ ")*1
You should insert the blank character after @!
Paul.
Thanks for the help.
It took a little bit of try & error to do without syntax error messages but it works like a charm once I got it and will be helpful in the future.
Den
How parse Numbers cell into two cells