Excel Question: Extracting middle text

1,332 Views | 11 Replies | Last: 7 yr ago by BBQ4Me
BBQ4Me
How long do you want to ignore this user?
AG
I'm trying to extract a number from the middle of of a cell where the preceding characters in the cell are of varying length.

A1 = <some characters><5 digit number><#><9 characters>

I've tried:
=RIGHT(A1, 15) - RIGHT(A1, 5)
That gave me an error

Any thoughts?


BusterAg
How long do you want to ignore this user?
AG
use a combination of mid and find

=Mid(A1,Find("#",A2)-9,9) or something like that
BusterAg
How long do you want to ignore this user?
AG
Or, if you know the length of the right side of the cell, you can use mid and len

=Mid(a1, len(a1)-15,9)
Ark03
How long do you want to ignore this user?
AG
BusterAg said:

Or, if you know the length of the right side of the cell, you can use mid and len

=Mid(a1, len(a1)-15,9)
Actually, your version returns 9 characters - I think the OP was looking for 5. Mine returns the five characters preceding the 9 digits at the end. If there are more than 9 characters at the end, we need some other identifier in your data set.
Quote:

=MID(A1,(LEN(A1)-13),5)
BBQ4Me
How long do you want to ignore this user?
AG
This worked!

Thank you very much BusterAg and Ark03.
BBQ4Me
How long do you want to ignore this user?
AG
I have one more related question - this time for returning characters in the middle where the preceding length of characters varies and the text I'm extracting varies in length. However, there is an identifier (&Nyy=) that precedes what I'm trying to extract.

A1 = <some varying length of chars><&Nyy><target of varying length to extract><some varying length of chars>

I've been searching online, but haven't been able to locate a solution. Thanks for any thoughts you have!
BEaggie08
How long do you want to ignore this user?
AG
=MID(A1,FIND("&Nyy",A1)+4,#)

Where the "#" is you either put a number or come up with some kind of logic to determine what the length is going to be. You haven't provided enough info for the logic.

Is there something that separates or differentiates the text you're trying to extract from the "varying characters" at the end?
Ark03
How long do you want to ignore this user?
AG
BEaggie08 said:

=MID(A1,FIND("&Nyy",A1)+4,#)

Where the "#" is you either put a number or come up with some kind of logic to determine what the length is going to be. You haven't provided enough info for the logic.

Is there something that separates or differentiates the text you're trying to extract from the "varying characters" at the end?
This.

The only way this would be doable is to have identifiers where you could differentiate the start and the stop of the text within the string that you'd like to extract. Either identifiers at the beginning and end, or a start and a length would work - you just need logic that could be applied to each cell.
VikingNik
How long do you want to ignore this user?
AG
couldn't you nest a left(right())? left(right(A1,15),5) or whatever? Everything is a known length from the end of the string or am I reading the problem statement wrong?
Ark03
How long do you want to ignore this user?
AG
VikingNik said:

couldn't you nest a left(right())? left(right(A1,15),5) or whatever? Everything is a known length from the end of the string or am I reading the problem statement wrong?
OP said:
Quote:

I have one more related question - this time for returning characters in the middle where the preceding length of characters varies and the text I'm extracting varies in length. However, there is an identifier (&Nyy=) that precedes what I'm trying to extract.

A1 = <some varying length of chars><&Nyy><target of varying length to extract><some varying length of chars>

So, varying length of characters before the string, varying length of characters in the string, and varying length of characters after the string. You know the string begins after <&Nyy>, but how do you know when the target string ends?

If I read the OP right, here are three sample strings that point out the problem as presented: if all I want are the capital A's, how do I write logic to pull them out (short of knowing what my target string is)?

ab&NyyAAAAabcd
abc&NyyAAabcde
a&NyyAAAabc

Here are some examples of what you would need to find the A's:
  • Something unique about the target string itself (ie data in the string always ends in a character not found anywhere else, or data in the target string is all alpha while everything else is numeric)
  • Specific length of the target string
  • Specific length of data after the target string
BBQ4Me
How long do you want to ignore this user?
AG
Thanks all for the replies!

Here's an example of what I'm trying to do:
proj?Ntx=mode%2Bmatchallpartial&Ntk=Text%20Search&N=0&Nyy=SEARCHTERM&No=0

proj?Ntx=mode%2Bmatchallpartial&rpp=25&Ntk=Text%20Search&Ne=8068+4294339056+8553+4294607117&N=0&No=25&Nyy=SEARCHTERM


proj?Ntx=mode%2Bmatchallpartial&rpp=50&Ntk=Text%20Search&N=0&No=0&Nyy=SEARCHTERM&Ne=8068+4294339056+8553+4294607117


I'm trying to extract the SEARCHTERM from each row.
As far as identifiers, I've found the following:
-Always follows "&Nyy="
-Sometimes it ends with a "&" and other characters; sometimes nothing follows it
BEaggie08
How long do you want to ignore this user?
AG
There is probably a better way, but give this a shot:

=IFERROR(MID(A1,FIND("&Nyy=",A1)+5,FIND("&",A1,FIND("Nyy=",A1))-FIND("&Nyy=",A1)-5),MID(A1,FIND("&Nyy=",A1)+5,LEN(A1)-FIND("&Nyy=",A1)+5))
BBQ4Me
How long do you want to ignore this user?
AG
Awesome! That worked. I had come up with almost the same syntax, except I didn't know about the IFERROR function. That fixed the errors I was encountering.

Again, thank you all very much for your help!
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.