Easy Problem b/c I am not an Excel Expert

[Anthony232]

Here is my problem...Included at the end of our product descriptions, w
sometimes include "ETA's". For example, our product description (whic
can be up to 300 characters) could read"

1969 Corvette Bumper - Chrome Retainers included - quality rating of 1
- ETA: 07/30/12.

What I am trying to do is to remove the ETA: 07/30/12. I tried to Fin
and Replace all "ETA:" with about 500 @ symbols. Then, I was going to d
Text to Columns and choose Fixed Width...setting the cutoff point at th
longest product description in the sheet...at character # 301 fo
example. Doing this would push everything after the ETA way to th
right. I was then going to delete the second column that i
created...getting rid of most of the @ symbols, and ALL dates. Finally
I would Find and Replace all @ symbols in the first (original) colum
leaving me with just the description.

I know this is the long way, but I am no Excel Expert, so this is on
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 AN
the ETA in the word rETAiners. I thought that if I used "" around th
actual letters that I am looking for, it would ONLY replace exac
matches....retainers is NOT an exact match for ETA, and no matter what
try, it will not replace ETA without replacing rETAiners also!

Just to break my problem down and figure out how to single out exac
matches, I was using the FIND function and trying to FIND exact matche
for "ETA:" and it kept coming up with an error saying that exce
couldn't find any matches. However, if I did a FIND for just ETA, i
found them all...standalone and in words.

So, if you can help me with my original plan, that would be ok, but i
you could help me delete EVERYTHING after the word ETA with blanks, tha
would be a lot easier. Another problem is that sometimes its ETA, an
other times it's ETA: So, I think I will have to run whatever proces
you help me come up with twice. Once for the exact match of ETA, an
again with the exact match of ETA:

Thank you SOOOOOOOOO much...I have spent about 3 hours on this so fa
and my deadline was yesterday!

.:

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Auric__]

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.

 

[isabelle]

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 :

 

[Martin Brown]

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.

 

[thompson759]

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





+-------------------------------------------------------------------+

+-------------------------------------------------------------------+

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