Very Urgent - Find / Search Help Needed

[TGV]

I am having the below mentioned data in A1 & A2 cells now i want to extract
the last values from the right side which is lying after the dot (.). The
values i require is "jjjjlkk" & "ajaslfasdld".

abc.2323.zzzzzz.gyhjiol.ksdfsl.6546.jjjjlkk
sdfjas.46465.asdjasdf.dfsjsafl.sdfjslf.sdjsalfasdl.ajaslfasdld

Please suggest me how i can do this...

Thanks in advance.

TGV

 

[Per Jessen]

Hi

Select the cells and goto Data > Text to Columns > Delemited > Next >
Delemiters: Check "Other" and enter a dot (.) > Next > In Data preview the
first column is selected; Column data format: Do not import column. Repeat
for all columns but the last. > Change destination if you want to keep
original data. > Finish

Hopes this helps
....
Per

 

[Jacob Skaria]

With your data in cell A1 try the below formula

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

If this post helps click Yes

 

[TGV]

Hi Jacob,

Thaks for your reply and this is what i needed.

TGV

 

[TGV]

Jacob,

Another one help needed here please tell me how to extract the data from
left side upto 3rd dot (.) i.e abc.2323.zzzzzz.

The values i require is "abc.2323.zzzzzz" & "sdfjas.46465.asdjasdf"

abc.2323.zzzzzz.gyhjiol.ksdfsl.6546.jjjjlkk
sdfjas.46465.asdjasdf.dfsjsafl.sdfjslf.sdjsalfasdl.ajaslfasdld

Please suggest me...

Thanks in advance.

TGV

 

[Jacob Skaria]

Try
=LEFT(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1)

If this post helps click Yes

 

[TGV]

THANK U VERY MUCH

FINAL QUESTION "JACOB"

Jacob,

Another one help needed here please tell me how to extract the data from
MIDDLE side FROM 3rd dot to before the last dot (.) i.e gyhjiol.ksdfsl.6546.

The values i require is "gyhjiol.ksdfsl.6546" &
"asdjasdf.dfsjsafl.sdfjslf.sdjsalfasdl"

abc.2323.zzzzzz.gyhjiol.ksdfsl.6546.jjjjlkk
sdfjas.46465.asdjasdf.dfsjsafl.sdfjslf.sdjsalfasdl.ajaslfasdld

Please suggest me...

Thanks in advance.

TGV

 

[Jacob Skaria]

If the number of dots are fixed then try Data>text to columns..and
concatenate to get back what you want. If your requirement is for a fomula
then try..the below which is a combination of the earlier two formulas...

=SUBSTITUTE(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1))&REPT("~",10),"."&TRIM(RIGHT(SUBSTITUTE(A1,".",REPT("
",LEN(A1))),LEN(A1)))&REPT("~",10),"")


If this post helps click Yes

 

[Teethless mama]

Try this:

=TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),297,297))