Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Easy Problem b/c I am not an Excel Expert
Here is my problem...Included at the end of our product descriptions, we sometimes include "ETA's". For example, our product description (which can be up to 300 characters) could read"
1969 Corvette Bumper - Chrome Retainers included - quality rating of 10 - ETA: 07/30/12. What I am trying to do is to remove the ETA: 07/30/12. I tried to Find and Replace all "ETA:" with about 500 @ symbols. Then, I was going to do Text to Columns and choose Fixed Width...setting the cutoff point at the longest product description in the sheet...at character # 301 for example. Doing this would push everything after the ETA way to the right. I was then going to delete the second column that it created...getting rid of most of the @ symbols, and ALL dates. Finally, I would Find and Replace all @ symbols in the first (original) column leaving me with just the description. I know this is the long way, but I am no Excel Expert, so this is one way for me to get it done...but here's my problem.... Looking at the example above, I tried to use the formula =Substitute(A1, ("ETA"), "@@@@@@") BUT, when I use this formula, it replaces the ETA AND the ETA in the word rETAiners. I thought that if I used "" around the actual letters that I am looking for, it would ONLY replace exact matches....retainers is NOT an exact match for ETA, and no matter what I try, it will not replace ETA without replacing rETAiners also! Just to break my problem down and figure out how to single out exact matches, I was using the FIND function and trying to FIND exact matches for "ETA:" and it kept coming up with an error saying that excel couldn't find any matches. However, if I did a FIND for just ETA, it found them all...standalone and in words. So, if you can help me with my original plan, that would be ok, but if you could help me delete EVERYTHING after the word ETA with blanks, that would be a lot easier. Another problem is that sometimes its ETA, and other times it's ETA: So, I think I will have to run whatever process you help me come up with twice. Once for the exact match of ETA, and again with the exact match of ETA: Thank you SOOOOOOOOO much...I have spent about 3 hours on this so far and my deadline was yesterday! .:M |
#2
|
|||
|
|||
Quote:
Have a look at the attached. On the proviso that all of your data follows the same format this should work. It doesn't matter how many characters are in each cell, just that they end in " - ETA: MM/DD/YY" i.e. 16 characters. There are other ways where you can get Excel to ignore characters after a certain point, i.e. anything after " - ETA:" but if your data is pretty uniform then the formula above is probably the easiest. Let me know if this works for you. |
#3
|
|||
|
|||
Quote:
After re-reading your original post, it would appear that the ETD: etc. part of the data is only there sometimes. This version will extract that leaving only the rest when it DOES appear and will leave the text intact when it DOESN'T appear. Hope that's of help. |
#4
|
|||
|
|||
Quote:
So, I would love for you to show me how to delete everything (no matter the number of characters) after ETA: We have many many people here entering data, so sometimes dates are noted as 07/25/12, sometimes it's 07/25, sometimes it's 7/25/2012...so, as long as I can delete everything after ETA...I would be where I needed to be. Thanks for your quick reply, and sorry I wasn't clear. .:M |
#5
|
|||
|
|||
Quote:
The second example I posted does just that. Attached again for convenience. This version will strip the ETA: part out of the text string IF it exists, if it doesn't then it will just copy the text string as it is. Let me know how you get on with it. S. |
#6
|
|||
|
|||
Quote:
This is perfect! Thanks for the help, it's much much appreciated! .:M |
#7
|
|||
|
|||
Not a problem. Glad to be of assistance. :)
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Problem b/c I am not an Excel Expert
Anthony232 wrote:
Here is my problem...Included at the end of our product descriptions, we sometimes include "ETA's". For example, our product description (which can be up to 300 characters) could read" 1969 Corvette Bumper - Chrome Retainers included - quality rating of 10 - ETA: 07/30/12. What I am trying to do is to remove the ETA: 07/30/12. I tried to Find and Replace all "ETA:" with about 500 @ symbols. Then, I was going to do Text to Columns and choose Fixed Width...setting the cutoff point at the longest product description in the sheet...at character # 301 for example. Doing this would push everything after the ETA way to the right. I was then going to delete the second column that it created...getting rid of most of the @ symbols, and ALL dates. Finally, I would Find and Replace all @ symbols in the first (original) column leaving me with just the description. I know this is the long way, but I am no Excel Expert, so this is one way for me to get it done...but here's my problem.... Looking at the example above, I tried to use the formula =Substitute(A1, ("ETA"), "@@@@@@") BUT, when I use this formula, it replaces the ETA AND the ETA in the word rETAiners. I thought that if I used "" around the actual letters that I am looking for, it would ONLY replace exact matches....retainers is NOT an exact match for ETA, and no matter what I try, it will not replace ETA without replacing rETAiners also! To solve your immediate problem, try replacing "Retainers" with something not used elsewhere on the sheet, then replacing "ETA", the replacing that something with "Retainers", like this (1 line, watch the word wrap): =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Retainers"," ~"),"ETA","@@@@@@"), "~","Retainers") (It doesn't matter what character you use in place of "~", as long as it's not already in use.) Just to break my problem down and figure out how to single out exact matches, I was using the FIND function and trying to FIND exact matches for "ETA:" and it kept coming up with an error saying that excel couldn't find any matches. However, if I did a FIND for just ETA, it found them all...standalone and in words. So, if you can help me with my original plan, that would be ok, but if you could help me delete EVERYTHING after the word ETA with blanks, that would be a lot easier. Another problem is that sometimes its ETA, and other times it's ETA: So, I think I will have to run whatever process you help me come up with twice. Once for the exact match of ETA, and again with the exact match of ETA: To find out what character is being displayed after the "ETA", run this bit of VB code: Sub whatChar() x = InStr(Range("A1").Value, "ETA") MsgBox Asc(Mid(Range("A1").Value, x + 3)) End Sub If the msgbox doesn't say 58, then you have some alternate character there. -- Hang on a second, I need to make my eyes revert to normal. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Problem b/c I am not an Excel Expert
hi,
Sub Macro1() t1 = "1969 Corvette Bumper - Chrome Retainers included - quality rating of 10- ETA: 07/30/12." t2 = "ETA " & Split(t1, "ETA")(1) End Sub -- isabelle Le 2012-07-25 12:30, Anthony232 a écrit : Here is my problem...Included at the end of our product descriptions, we sometimes include "ETA's". For example, our product description (which can be up to 300 characters) could read" 1969 Corvette Bumper - Chrome Retainers included - quality rating of 10 - ETA: 07/30/12. What I am trying to do is to remove the ETA: 07/30/12. I tried to Find and Replace all "ETA:" with about 500 @ symbols. Then, I was going to do Text to Columns and choose Fixed Width...setting the cutoff point at the longest product description in the sheet...at character # 301 for example. Doing this would push everything after the ETA way to the right. I was then going to delete the second column that it created...getting rid of most of the @ symbols, and ALL dates. Finally, I would Find and Replace all @ symbols in the first (original) column leaving me with just the description. I know this is the long way, but I am no Excel Expert, so this is one way for me to get it done...but here's my problem.... Looking at the example above, I tried to use the formula =Substitute(A1, ("ETA"), "@@@@@@") BUT, when I use this formula, it replaces the ETA AND the ETA in the word rETAiners. I thought that if I used "" around the actual letters that I am looking for, it would ONLY replace exact matches....retainers is NOT an exact match for ETA, and no matter what I try, it will not replace ETA without replacing rETAiners also! Just to break my problem down and figure out how to single out exact matches, I was using the FIND function and trying to FIND exact matches for "ETA:" and it kept coming up with an error saying that excel couldn't find any matches. However, if I did a FIND for just ETA, it found them all...standalone and in words. So, if you can help me with my original plan, that would be ok, but if you could help me delete EVERYTHING after the word ETA with blanks, that would be a lot easier. Another problem is that sometimes its ETA, and other times it's ETA: So, I think I will have to run whatever process you help me come up with twice. Once for the exact match of ETA, and again with the exact match of ETA: Thank you SOOOOOOOOO much...I have spent about 3 hours on this so far and my deadline was yesterday! .:M +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Problem b/c I am not an Excel Expert
On 25/07/2012 22:21, isabelle wrote:
hi, Sub Macro1() t1 = "1969 Corvette Bumper - Chrome Retainers included - quality rating of 10- ETA: 07/30/12." t2 = "ETA " & Split(t1, "ETA")(1) End Sub Splitting on "ETA:" would be safer and forcing match case would help. =LEFT(A1, FIND("ETA:", A1,1)-2) Would be my choice in a worksheet functions. Needs some work to defend against ETA not being found etc. -- Regards, Martin Brown |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy Problem b/c I am not an Excel Expert
On Wednesday, July 25, 2012 12:30:44 PM UTC-4, Anthony232 wrote:
Here is my problem...Included at the end of our product descriptions, we sometimes include "ETA's". For example, our product description (which can be up to 300 characters) could read" 1969 Corvette Bumper - Chrome Retainers included - quality rating of 10 - ETA: 07/30/12. What I am trying to do is to remove the ETA: 07/30/12. I tried to Find and Replace all "ETA:" with about 500 @ symbols. Then, I was going to do Text to Columns and choose Fixed Width...setting the cutoff point at the longest product description in the sheet...at character # 301 for example. Doing this would push everything after the ETA way to the right. I was then going to delete the second column that it created...getting rid of most of the @ symbols, and ALL dates. Finally, I would Find and Replace all @ symbols in the first (original) column leaving me with just the description. I know this is the long way, but I am no Excel Expert, so this is one way for me to get it done...but here's my problem.... Looking at the example above, I tried to use the formula =Substitute(A1, ("ETA"), "@@@@@@") BUT, when I use this formula, it replaces the ETA AND the ETA in the word rETAiners. I thought that if I used "" around the actual letters that I am looking for, it would ONLY replace exact matches....retainers is NOT an exact match for ETA, and no matter what I try, it will not replace ETA without replacing rETAiners also! Just to break my problem down and figure out how to single out exact matches, I was using the FIND function and trying to FIND exact matches for "ETA:" and it kept coming up with an error saying that excel couldn't find any matches. However, if I did a FIND for just ETA, it found them all...standalone and in words. So, if you can help me with my original plan, that would be ok, but if you could help me delete EVERYTHING after the word ETA with blanks, that would be a lot easier. Another problem is that sometimes its ETA, and other times it's ETA: So, I think I will have to run whatever process you help me come up with twice. Once for the exact match of ETA, and again with the exact match of ETA: Thank you SOOOOOOOOO much...I have spent about 3 hours on this so far and my deadline was yesterday! :M +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- Anthony232 If you would like a worksheet formula, try this two step approach: This assumes the source data is in cell B2. 1969 Corvette Bumper - Chrome Retainers included - quality rating of 10 - ETA: 07/30/12 In cell C2, find the starting position of the text: =FIND(" - ETA",B2,1) In Cell D2, get the text you want: =LEFT(B2,C2) M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can's work this out, probably easy for an expert! | Excel Discussion (Misc queries) | |||
Hopefully easy prob for an excel expert | Excel Programming | |||
simplification problem (only for expert!) | Excel Programming | |||
EASY - Excel VBA Problem | Excel Programming |