seperate a value in a text string

[Guest]

I need to remove a value in a cell and place only that value in the next
cell, example:

Column One Column Two
CS/4 BX/40 EA 40
CS/72 EA 72
EA 1

Is there an IF statement that can do this? I have 1200 rows of these and I
need to just have the last number in column two and where there is text(EA) I
need it to show the number 1? Can this be done?

 

[Carim]

Hi Thomas,

=IF(LEN(A1)=2,1,VALUE(MID(A1,LEN(A1)-4,2)))

should do the job ...

HTH
Cheers
Carim

 

[JE McGimpsey]

Will there always be "EA" at the end of the string? If so

=IF(A2="EA", 1, --TRIM(MID(LEFT(A2, LEN(A2) - 2), FIND("%",
SUBSTITUTE(A2,"/","%", LEN(A2) - LEN(SUBSTITUTE(A2,"/",""))))+1, 255)))

appears to work...

 

[Guest]

Nice one JE......and it will accomodate 3 or more place numbers as
well......such as
CS/4 BX/12340 EA


Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thank you sooooo much. I wasn't really sure how to put the functions
together to make it all work.

 

[Guest]

No, it could have cs, pk, etc also, what can you use other than listing them
all out in the IF statement?

 

[Guest]

Carim,

What if the value is BX/5 EA, It worked for BX/50 EA but not the other, what
do I need to change in the string to make it work?

 

[Guest]

There has to be some consistancy in the data in order to write rules into
formulas to allow Excel to extract the desired parts. All of your samples
showed the cells ending in EA, and now you say they all don't........perhaps
if you would give a larger sample of your data, more representative of the
extremes, it would help.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

CLR,

Very true sir,

I did not relize this until I got the error's. I did get it to work though,
now I just need to combine some rules and statements to make it work as one.
Thanks to all for the help, this was a learning one for me and now I know how
it works.

 

[Guest]

No problem Thomas, I did not mean to sound critical..........I'm glad you got
it working. Please don't hesitate to come back if we can be of any further
assistance.

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

I need to remove a value in a cell and place only that value in the next
cell, example:

Column One Column Two
CS/4 BX/40 EA 40
CS/72 EA 72
EA 1

Is there an IF statement that can do this? I have 1200 rows of these and I
need to just have the last number in column two and where there is text(EA) I
need it to show the number 1? Can this be done?

To extract the last number from a cell, or show a 1 if there no number in the
cell, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

This add-in can also be easily distributed with your workbook, if that is a
requirement.


Then use this Regular Expression formula:

=--IF(REGEX.MID(A1,"\d+",-1)="",1,REGEX.MID(A1,"\d+",-1))


--ron