G'Day
I have a whole lot of formulas that I used to use regularly in Numbers however when I copy/paste them now they no longer work. I have attached these so if anyone is able to spot why these no longer work that would be a huge. For this specific issue I need to extract data to the right of the -. See attached. Thank you
MacBook Pro 13", macOS 10.15
Hi ctnic,
If the separator is always space hyphen space, try this.
Formula in B2 =LEN(A2)
Formula in C2 =FIND(" - ",A2,1)
Formula in D2 =LEFT(A2,C2−1)
Formula in E2 =RIGHT(A2,B2−C2−3)
Hide the intermediate columns if you want.
Regards,
Ian.
Hi ctnic,
If the separator is always space hyphen space, try this.
Formula in B2 =LEN(A2)
Formula in C2 =FIND(" - ",A2,1)
Formula in D2 =LEFT(A2,C2−1)
Formula in E2 =RIGHT(A2,B2−C2−3)
Hide the intermediate columns if you want.
Regards,
Ian.
So interestingly enough, the hyphen is some other sort of character. This is what I have to deal with regularly from customers. Their data is can be so whack sometimes. I changed it to a regular hyphen and it worked. I've just copied whatever that other character was and just did find/replace. Worked a treat. Thanks Gents
Thanks for your reply Wayne. Still just getting errors. I used to use a single formula for this rather than have multiple columns. the issue started after the previous Numbers update. Appreciate your input nonetheless.
Thanks for your reply Ian. I can't seem to get that to work. I don't need data in BC & D. Any ideas?
to locate the "delimiter" (which is, in your case, the " - ", space dash space) you can do this:
I prefer to use extra columns because they simplify the problem.
So I added column B where the start of the delimiter is found:
B3=FIND(" - ", A3, 1)
C3 will contain the formula to identify the "left" part of the phrase
C3=LEFT(A3, B3−1)
The right side:
D3=RIGHT(A3, LEN(A3)−B3−3+1)
maybe you'd like to share the error. Click the triangle in the cell reporting the error and tell us what the error is.
Also a screenshot of the selected cell with the error is helpful
Since I posted (and so did Ian) formulas that are working, maybe you should try the formulas we posted. Once the problem is understood you can re-arrange back to a single formula
Extract data to the right or left of a character
