Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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? -- Thomas |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
Hi Thomas,
=IF(LEN(A1)=2,1,VALUE(MID(A1,LEN(A1)-4,2))) should do the job ... HTH Cheers Carim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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... In article , T Miller wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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 "JE McGimpsey" wrote: 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... In article , T Miller wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
Thank you sooooo much. I wasn't really sure how to put the functions
together to make it all work. -- Thomas "Carim" wrote: Hi Thomas, =IF(LEN(A1)=2,1,VALUE(MID(A1,LEN(A1)-4,2))) should do the job ... HTH Cheers Carim |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
No, it could have cs, pk, etc also, what can you use other than listing them
all out in the IF statement? -- Thomas "JE McGimpsey" wrote: 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... In article , T Miller wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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? -- Thomas "Carim" wrote: Hi Thomas, =IF(LEN(A1)=2,1,VALUE(MID(A1,LEN(A1)-4,2))) should do the job ... HTH Cheers Carim |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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 "T Miller" wrote: No, it could have cs, pk, etc also, what can you use other than listing them all out in the IF statement? -- Thomas "JE McGimpsey" wrote: 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... In article , T Miller wrote: 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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. -- Thomas "CLR" wrote: 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 "T Miller" wrote: No, it could have cs, pk, etc also, what can you use other than listing them all out in the IF statement? -- Thomas "JE McGimpsey" wrote: 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... In article , T Miller wrote: 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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
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 "T Miller" wrote: 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. -- Thomas "CLR" wrote: 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 "T Miller" wrote: No, it could have cs, pk, etc also, what can you use other than listing them all out in the IF statement? -- Thomas "JE McGimpsey" wrote: 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... In article , T Miller wrote: 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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
seperate a value in a text string
On Wed, 4 Oct 2006 09:04:02 -0700, T Miller
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace text format | Excel Discussion (Misc queries) | |||
Finding specific text in a string | Excel Worksheet Functions | |||
Remove last character of text string | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions |