Can I extract a symbol and number from a cell?

Hi Sharon,

It depends on what follows #123 or #1234.

If it's always the same character, you can use FIND to determine where the title hould be split,and work from there.

If there is no such constant marker, and the 'number' part of the title varies in length, then you will need to detect where each number ends and each title begins.

A sample of the data would be useful in determining a solution. Please include the smallest and the largest item number you have to deal with, plus some examples with numbers in between. Also include the two parts you want each example to be split into.

Regards,

Barry

Update:

Here's an example using each of the two methods shown above.

Original data is in column A.

Columns B and C (yellow) use FIND to locate the "a" that, in this sample, immediately follows the last digit of the item number.

Columns D and E (blue) use an error trap to decide if the item number has 5 or 4 characters.

As can be seen, both return the same results.

Formulas:

B2: =LEFT(A,FIND("a",A)-1)

C2: =RIGHT(A,LEN(A)-(FIND("a",A)-1))

D2: =LEFT(A,IF(ISERROR(MID(A,5,1)*1),4,5))

E2: =RIGHT(A,LEN(A)-IF(ISERROR(MID(A,5,1)*1),4,5))

Each formula is entered in row 2 of its column, then filled down to the end of that column.

Regards,

Barry

Hi Sharon,

Ahh! Just as you wrote it in your original post, except for the space after # in this version, which wasn't there in the first example.

Fortunately the space after the # makes no difference in the formulas below. As can be seen in rows 14, 15 and 15 where I've used the name #num pattern in place of the name # num in the other rows, the formula transfers the number in the same pattern as used in the original.

Formulas:

B2: =LEFT(A,FIND("#",A)-2)

FIND looks for # in the string in column A, and returns the number corresponding to its position in that string. -2 adjusts that value to remove the # and the space before it from that count. The result is passed to LEFT.

LEFT starts at the beginning of the string in A, and returns the number of characters specified by the result above.

C2: =RIGHT(A,LEN(A)-(FIND("#",A)-1))

LEN(A) returns the length (in number of characters) of the string in A.

FIND looks for # in the string in column A, and returns the number corresponding to its position in that string. -1 adjusts that value to remove the # from the count. the result is subtracted from LEN(A), and the difference is passed on to RIGHT as the number of characters to return from the end of the string in A.

The formulas in B2 and C2 are filled down to the ends of their respective columns.

Note: Both formulas will return 'could not find' errors where there is no entry in column A or where the entry in column A does not include a # character. This is expected.

Regards,

Barry

Hi Sharon,

You can also do this with a select, a click, and a paste -- no formulas and no AppleScript knowledge needed.

1. Copy-paste script below into Script Editor (in Applications > Utilities).
2. Select the cells in the column with the data (in my example A2:A5).
3. Click the 'run' button in Script Editor.
4. Click once in a destination cell (in my example B2)
5. Type Command-v (or choose Edit > Paste and Match and Match Style) to Paste.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range) to ¬

set vv to selection range'scells'svalue

end tell

end tell

set pasteStr to ""

set text item delimiters to " "

repeat with v in vv

set t to v's contents

set col1 to t'swords 1 thru -2 as text-- -2 means "second to last"

set col2 to "#" & t's last word

set pasteStr to pasteStr & col1 & tab & col2 & return

end repeat

set text item delimiters to ""

set the clipboard topasteStr

"that BEAUTIFUL formula that looks so good in your illustration gave me a stupid error message: FIND couldn’t find the value “#”.

I tried it two ways: copy/paste and typing it without copy/paste. Neither worked."

Generally that error comes up when the formula is looking in the wrong place, or the value being looked for does not occur in the target string.

Are the long titles ( a better product # 1278 ) in Column A?

If not, does the formula reference the column which does contain the long titles? (Twice in the LEFT formula, three times in the RIGHT formula)

Do the formulas work if you copy the # from one of the long titles, then paste it in place of the # in the formula?

Regards,

Barry

Hi Sharon,

Happy to hear it's working for you. Thanks for the feedback and the green check.

Points are awarded when you click the 'solved' checkmark on the post that solved the question, or the "this helped me" line at the bottom of a post that helped solve the problem.

As the Original Poster, you can mark one post as the solution, and up to two posts as "helped."

In a thread where you did not initiate the question, you can mark at least one post as "helped." I don't know if you can mark more than one, as I've not tried that yet.

Apple can also mark posts as 'recommended.' I'm not sure if the Hosts, the Community Specialists, or some other Apple rep does that marking, or if there's a limit to the number of posts they can mark (I've seen at least one thread with three 'recommended' posts.

The current point schedule is 10 for 'solved,' 5 for 'helped' and 7 for 'recommended.'

Regards,

Barry

Hi Sharon,

Thanks for the gold star, and for the gracious feedback!

SG