copying everything after "." only

[zohaib]

Hello,

Please see attachment for what I am trying to do. I have the formula in column b. I only want everything after "." (period) but it is not working correctly. I dont know what is wrong. thanks

Thank you,
Zohaib

 

[RSidhu]

I would use text to column, using "." as a delimited character

 

[zohaib]

Hello RSidu,

What about macro? How would I do this in macro? Thanks

 

[tomas]

Hi

see if this helps

 

[NARAYANK991]

Hi ,

The point is that finding the "." finds the first of many period characters in your data.

If you are sure that the set of period characters will always be followed by a space character , then use the string ". " as the parameter to the Find function.

Changing your existing formula to :

=RIGHT(A2,LEN(A2)-FIND(". ",A2)-1)

will work.

Narayan

 

[Deepak]

Just another approach!

=TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",299)),299))

 

[Somendra Misra]

Hi,

Just another method (a bit long array formula, so must be entered with Ctrl+Shift+Enter.

=TRIM(RIGHT(A2,MIN(IF(((TRIM(MID(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))),1,1))=".")*ROW(INDIRECT("1:"&LEN(A2))))<>0,((TRIM(MID(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))),1,1))=".")*ROW(INDIRECT("1:"&LEN(A2))))))-1))

or another array formula 3 character shorter.

=TRIM(MID(A2,FIND("|",INDEX(SUBSTITUTE(A2,".","|",ROW(INDIRECT("1:"&(LEN(A2)-LEN(SUBSTITUTE(A2,".","")))))),COUNTA(SUBSTITUTE(A2,".","|",ROW(INDIRECT("1:"&(LEN(A2)-LEN(SUBSTITUTE(A2,".","")))))))))+1,255))



Regards,

 

[zohaib]

Thanks to all for your help