How to extract parsed data from once cell to another

Question

Given a spreadsheet cell containing a string that consists of a hyphenated series of character segments, I need to extract the last segment.

For example, consider column A containing data strings like XX-XXX-X-XX-XX-G10, where X denotes any character. What formula would I need to place in column B to get G10 as a result?

            A                B
1   XX-XXX-X-XX-XX-G10      G10

I'm looking for a formula that could work in in Libre Office Calc, Open Office Calc, MS Excel, or Google Sheets.

Glad to - wow, 5 answers! (Of course, if you'd mentioned that the part you wanted was always 3 characters, it would have been much, much, much simpler!) — Mogsdad, Jun 29 '16 at 13:43
Yeah. I'm still learning SO ;) to be more precise in problem explanation. Many thanks again for help and time @Mogsdad (im worried to be downvoted ass hell! ;D) — PipBoy2000, Jun 29 '16 at 14:31

score 2 · Answer 1 · answered Jun 28 '16 at 19:13

2

If you are using google sheets, regexextract would be possible too:

=REGEXEXTRACT(A1, "[^-]+$")

answered Jun 28 '16 at 19:13

JPV

26,499
4
33
48

1

Most elegant solution - i'm really missing a regex-capable replace function in LO Calc! – tohuwawohu Jun 28 '16 at 19:25

score 2 · Answer 2 · answered Jun 28 '16 at 19:17

In LibreOffice Calc and OpenOffice Calc, you can use a regular expression to determine the position of the text after the last - character:

=SEARCH("-[:alnum:]+$";A1)

will return 15 if A1 contains XX-XXX-X-XX-XX-G10.

Now, you can use this value to get the text "behind" that position, using the RIGHT() function:

=RIGHT(A1;LEN(A1)-SEARCH("-[:alnum:]+$";A1))

Split up on multiple lines:

=RIGHT(                ' return text beginning from the right...
    A1;                ' of cell A1 ...
    LEN(A1)            ' start at lenght(A1) = 18
    -                  ' minus ...
    SEARCH(            ' position ...
        "-[:alnum:]+$" ' of last "-" ...
        ;A1            ' in cell A1 = 15 ==> last three characters
    )
)

score 2 · Accepted Answer · edited Mar 20 '17 at 10:18

2

Another possibility in LO Calc is to use the general purpose regular expression macro shown here: https://superuser.com/a/1072196/541756. Then the cell formula would be similar to JPV's answer:

=REFIND(A1,"([^-]+$)")

edited Mar 20 '17 at 10:18

Community

1
1

answered Jun 28 '16 at 20:23

Jim K

12,824
2
22
51

And how about that case? http://stackoverflow.com/questions/38095773/how-to-extract-data-from-one-cell-to-another – PipBoy2000 Jun 29 '16 at 09:39

score 0 · Answer 4 · edited May 23 '17 at 11:54

It appears that you want the characters that appear at the end of a string, to the right of the last instance of a hyphen character, "-".

This formula, adapted from here, works in Excel, *Calc & Google Sheets:

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))

Explanation:

SUBSTITUTE(A1,"-",new_string) will find each hyphen ("-") in the original string from cell A1 and replace it with a new_string.
REPT(" ",LEN(A1)) is a string of repeated space characters (" "), the same length as the original string in cell A1.
TRIM(RIGHT(string,count)) will get the right-most count characters, and trim off leading and trailing spaces. Since the string was previously padded out by replacing hyphens with spaces, and count is the same LEN(A1) used for that padding, the last count characters consists of a bunch of spaces followed by whatever followed the last hyphen!

In Google Sheets, an alternative approach is to use the SPLIT function to break the value from column A into an array, then select the last element. (Excel-VBA has a split() function, so you could make this work in Excel by writing VBA code to provide it as a custom function.)

=INDEX(SPLIT(A1,"-"),0,COUNTA(SPLIT(A1,"-")))

score 0 · Answer 5 · answered Jun 29 '16 at 09:01

0

I found simply solution:

=RIGHT(A1;3)

that gives me G10 as the result too! It works because COL A always have 3 chars at the end!

answered Jun 29 '16 at 09:01

PipBoy2000

440
7
21

And how about that case? http://stackoverflow.com/questions/38095773/how-to-extract-data-from-one-cell-to-another – PipBoy2000 Jun 29 '16 at 09:38

How to extract parsed data from once cell to another

5 Answers5

Linked