Find a string in a cell and pasting it in a new column

[James]

Hi guys I have question. I have series of raw and I need to create a
new column based on a string in a cell. For example, I have this two
raws.

Adam-ID12345-kjkjjk-43343 |colomn| HHHHHHHHHHHHH |colomn|
KKKKKKKKKKKKKK
John-hhhh-43546456-ID232223- |colomn| yyyyyyyyyyyyyyyyy |
colomn| ssssssssss

I need so search ID in the first colomn, then create new column at the
end/beginning and past the ID in their so I can make the records
distinguishable from each other.

 

[OssieMac]

Hi James,

Could you post an example of what you want the data to look like after
separating the ID. I am not sure how much of the data represents an Id. Does
it always start with Id and then finishes at the next hyphen?

Example 1: Adam-ID12345-kjkjjk-43343
Is the ID "ID12345" or "ID12345-kjkjjk-43343"

Example2: John-hhhh-43546456-ID232223-
Is ID just "ID232223"

 

[OssieMac]

Hi Again James,

Assume Adam-ID12345-kjkjjk-43343-abdbdbdb is in cell A2. To extract "ID12345"

=MID(A2,FIND("ID",A2,1),FIND("-",A2,FIND("ID",A2,1))-FIND("ID",A2,1))

Now FIND is case sensitive. Can replace FIND with SEARCH which is not case
sensitive as per following.
=MID(A2,SEARCH("ID",A2,1),SEARCH("-",A2,SEARCH("ID",A2,1))-SEARCH("ID",A2,1))

 

[Rick Rothstein]

Here is another way to consider (FIND works fine in all situations with this
formula)...

=MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))-1)

--
Rick (MVP - Excel)

Hi James,

Could you post an example of what you want the data to look like after
separating the ID. I am not sure how much of the data represents an Id.
Does
it always start with Id and then finishes at the next hyphen?

Example 1: Adam-ID12345-kjkjjk-43343
Is the ID "ID12345" or "ID12345-kjkjjk-43343"

Example2: John-hhhh-43546456-ID232223-
Is ID just "ID232223"

--
Regards,

OssieMac







- Show quoted text -

Thanks OssieMac
What I need is the ID only. Here is an example what should be in the
first column of the first raw" Rows could be in the hundreds".
Adam-ID12345-kjkjjk-43343-abdbdbdb
The ID part will always be separated by the rest in the column by "-"

 

[James]

Hi Rick
It looks fine but here is my dilema. I need to write the results in a
column. Say in column G. How should I do that. It has been a while
since I used any kind of function.

 

[James]

I don't know if I mention this also or not at the beginning but the
ID could be anywhere in the field/cell, at the end or the begining.
The only thing that will stay the same is ID with numbers will be 8
characters and each side will be dash to saperate from the rest of the
information. Right now we only find it if it is a partuclar place.

 

[Rick Rothstein]

Put the formula I posted in G1 and copy it down (here I am assuming your
first piece of text is in A1). If your first piece of text is in, say, B2,
then put this formula (where I have replaced the A1 references with B2
references) in G2 and then copy it down...

=MID(G2,FIND("-",G2)+1,FIND("-",MID(G2,FIND("-",G2)+1,99))-1)

 

[Rick Rothstein]

Yes, as long as there is a dash on either side of the ID, what I posted will
work no matter where in the field it is located (and no matter how long the
ID is). Now, if the ID is **always** 8 characters long (your first example
showed an ID that was only 7 characters long by the way), then you can use
this much simpler formula...

=MID(A1,FIND("-",A1)+1,8)

 

[OssieMac]

yHi James,

Did you try the formulas that I posted. The look for the characters ID
wherever they are in the string.

To Rick,
James original post example showed the ID could have any number of hyphens
before the string ID.

 

[Rick Rothstein]

To Rick,

James original post example showed the ID could have any number of
hyphens before the string ID.

Hmm! So it did (I just went back and looked). Other than not noticing that,
I stayed away from the "ID" string, especially in upper or lower case (using
SEARCH instead of FIND), out of a worry that those letters could be located
in the text, as part of a larger word, in front of the ID text. I guess if
they are always upper case, the likelihood of them being in the text before
the actual ID is lessened. So, here is my formula modified to look for ID
(always upper case) instead of the dash...

=MID(A1,FIND("ID",A1),8)

I went with James latest post saying that the ID (letters plus following
numbers) was always 8 characters long.

 

[James]

Hmm! So it did (I just went back and looked). Other than not noticing that,
I stayed away from the "ID" string, especially in upper or lower case (using
SEARCH instead of FIND), out of a worry that those letters could be located
in the text, as part of a larger word, in front of the ID text. I guess if
they are always upper case, the likelihood of them being in the text before
the actual ID is lessened. So, here is my formula modified to look for ID
(always upper case) instead of the dash...

=MID(A1,FIND("ID",A1),8)

I went with James latest post saying that the ID (letters plus following
numbers) was always 8 characters long.

--
Rick (MVP - Excel)








- Show quoted text -

Thanks guys.
It works great.