Grab specific string from one cell and post it in another.

[Lazarus74]

Hi.

I have a cell that contains a path in a windows structure. In the path there is
a specific type of value that I would like to grab and post in another column.

Examples in column A:
C:\Folder1\Folder2\G000111\Folder3\test.doc
C:\Folder1\Folder2\G002111\Folder3\test.xls
C:\Folder11\Folder22\G000111 Sweden\Folder33\test.jpg

Now I would like to grab the "G00***" name and put it in column B.
One rule to be applied is that the folders names allways starts with "G00" (G, zero, zer0)

Is this possible to to since the names and the position in the string varies, so I can't set
a count for it to read from left X digits forward?

Many thanks in advance.

/Gherry

[ben_hensel]

A1 = text string of folder name & path
B1 = RIGHT(A1, LEN(A1) - SEARCH("G00", A1, 1) +1)

like that?

EDIT:

C1 = LEFT(B1, SEARCH("\", B1, 1) -1)

or like that?

[tigeravatar]

Alternate formula:

=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("G00",A1),255),"\",REPT(" ",99)),99))

[Lazarus74]

I'm a total noob at Excel, and maybe that is the reason I get an error on all of you guys suggested solutions.
None of them work, or rather, is accepted by Excel.

/Gherry

[nested.if1@gmail.com]

Try this one...
=MID(A1,FIND("G000",A1,1),(FIND("\",A1,FIND("G000",A1,1))-FIND("G000",A1,1)))

[Lazarus74]

All your posts worked fine, after some tips from vlady, but nested.if1:s worked the best.

I'm not that good at code but I can see that you break the find with "\" which is really nice, but what do I do
if I want to add more "breakpoints"? I really would like "\", " " and "(" also.

[vlady]

side note:

commas in formula don't work on the setting the op used ; instead

thanks.

[Lazarus74]

Sorry for being a bit messy, but I really want to learn.

I used =MID(A1,FIND("G000",A1,1),(FIND("\",A1,FIND("G000",A1,1))-FIND("G000",A1,1))) and what
I want now is for someone to explain to me what happens in this formula, piece by piece.
Does someone have the time?

Also: I really would like a formula that grabs the information between "\" number two and "\" number three
and present it in another column. As in example "C:\Folder11\Folder22\G000111 Sweden\Folder33\test.jpg"
it would be "Folder22". I can't search for it by name this time, since that varies.

/Gherry