Extract A String Between Two Characters

[theaudioguy]

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

 

[VISHMAY]

Thank You,

When I use first Formula It Was Working Perfect. But After few Days This Problem Comes
Thanks Again

I think this formula will do what you want and yet still cover all the possible combinations I can think of that you might have to account for...

=IF(ISNUMBER(FIND(".",LEFT(A1,FIND("?",A1&"?")-1))),TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("?",A1&"?")-1),".",REPT(" ",500)),500)),"")

 

[frustin]

Hi,

I'd like to jump on this bandwagon as well please.

My cells are vcards which i'm trying to split out into separated columns: Name, Organisation, Title, Telephone, Address, Email, Notes

example of A1 content:

BEGIN:VCARD
N:Joe Bloggs
ORG:Microstaff
TITLE:IT executive
TEL:0234522555
ADR:;;48 rue de Tomato;75032;;Paris;France
EMAIL:joe.bloggs@microstaff.com
NOTE:Contact from Cool Event London 2014
END:VCARD

As you can see the delimiters are a bit odd, i've imported this from an iPhone app called QRReader, but it didnt port to CSV very well.

I've tried all sorts of ways such as: =IF(SEARCH("N:",A2),SUBSTITUTE(A2,"N:",""),"") for the name field and so on but this does not work very well because it pulls in all the stuff after "bloggs".

I'm a bit of a newbie with these formulae. Any help appreciated thanks.

 

[István Hirsch]

If the first cell to process is A1, enter into B1 and drag it down and right:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,CHAR(10),":"),":",REPT(" ",999)),(1+2*COLUMN(A1))*999,999))

 

[Peter_SSs]

If the first cell to process is A1, enter into B1 and drag it down and right:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,CHAR(10),":"),":",REPT(" ",999)),(1+2*COLUMN(A1))*999,999))

I'd consider this slight modification to avoid the final "VCARD" if copied too far to the right.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"END:VCARD",""),CHAR(10),":"),":",REPT(" ",999)),(1+2*COLUMN(A1))*999,999))

 

[Peter_SSs]

Actually I'd make this further change so the formula doesn't fail if any new columns are added to the left of these formulas.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"END:VCARD",""),CHAR(10),":"),":",REPT(" ",999)),(1+2*COLUMNS($B1:B1))*999,999))

 

[István Hirsch]

Thanks, Peter. Well, I concentrated on the inside of the string as I do not know what the role and meaning of the VCARD frame (first and last line) is (are they constant or not, etc.).

 

[Peter_SSs]

Thanks, Peter. Well, I concentrated on the inside of the string as I do not know what the role and meaning of the VCARD frame (first and last line) is (are they constant or not, etc.).

Fair enough, I certainly made an assumption in that regard.

 

[RGMaveric]

Good Afternoon all, I'm going to jump on this band wagon if I may.
The information i'm looking for is going to be a string of numbers only the tricky thing is,

the string of number isn't always between the same two characters, any can the number of integers can very from 6 to 9

Here are some examples of what I'm working with

SP6BREG3NAKHAK|242925|SP6SREG3NAKHAK|1089
242925|SP6BREG3NAKHAK|1089

SUPERSAC|300021973|SQUATTOMAN|9004

<tbody>
</tbody>

I'd need to return the numbers in green

Thank you if anyone can help

 

[Rick Rothstein]

Here are some examples of what I'm working with

SP6BREG3NAKHAK|242925|SP6SREG3NAKHAK|1089
242925|SP6BREG3NAKHAK|1089

SUPERSAC|300021973|SQUATTOMAN|9004

<tbody>
</tbody>

Will there only be one such number per text string or could a single text string have multiple numbers to retrieve?

 

[RGMaveric]

just one string of numbers per line either 6 or 9, never 4