Re: removing value within a string

Ooops! Correction needed:

In that case all we need to do is find the location of the opening
parenthesis "(" in the string and extract everything to the right of that.

Should be:

In that case all we need to do is find the location
of the opening parenthesis "(" in the string and extract
everything to the LEFT of that.

Biff

"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message
news:%23XcIEwjVHHA.480@xxxxxxxxxxxxxxxxxxxxxxx
=SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0

This formula is simply replacing the 3 characters from the right with the
2 characters from the right. If:

A1 = 66014

Replace 3 characters from the right = 014
With 2 characters from the right = 14

Result = 6614

The result of the Substitute function is always a TEXT value. The +0
coerces the result to be NUMERIC.

The second formula:

=LEFT(A1,FIND("(",A1)-2)

I'm assuming that the portion of the string that you want to extract is
variable in length and that this portion is always present: (S)

TRAD EC JEANS (S) | CL11941033

In that case all we need to do is find the location of the opening
parenthesis "(" in the string and extract everything to the right of that.
In this example the "(" is the 15th character. So we subtract 2, one for
the "(" and one for the space before the "(". That results in 13. So the
formula is returning the first 13 characters starting from the left.

Biff

"Jane" <Jane@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1CD9E380-5070-4671-9A6A-2F638800F6EF@xxxxxxxxxxxxxxxx
and sorry I didn't combine my questions but for the 2nd solution also....
if
'from the right is need, why go with LEFT? and how does the -2 work into
it?
I just want to be able to understand it so I can explain it to some one
else
if asked and alter if needed for future use.

Thank you again! Jane

"T. Valko" wrote:

Try these:

1st problem:

=SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0

2nd problem:

=LEFT(A1,FIND("(",A1)-2)

Biff

"Jane" <Jane@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:035CD3B2-E8A2-4019-904A-5B55788B44E4@xxxxxxxxxxxxxxxx
I believe these problems have the same solution.

1st problem:
I would like to be able to remove the zero that is placed 3 digits
from
the
RIGHT. The values are:
66014
8004
8015

The values should look like:
6614
804
815

2nd problem in the same mproject:
I would like to be able to remove the values, text, and/ or spaces
that
are
16 places from the RIGHT.
The entry is:
TRAD EC JEANS (S) | CL11941033

The entry should look like:
TRAD EC JEANS

thank you for your help! :)








.

Relevant Pages

  • Re: isolate a number in cell with text and numbers
    ... That formula doesn't specifically extract numbers per se, ... have a string of numbers as *the last word*. ... The result of the inner TRIM function is then passed to the SUBSTITUTE ... string with other characters. ...
    (microsoft.public.excel.misc)
  • Re: Formula for extracting text formated numbers within ( )s as numbe
    ... To extract and return the number within the parenthesis which is a ?number ... My String (XYZ) where XYZ is of variable length. ... The parenthesis is always at the right terminal end of the string. ...
    (microsoft.public.excel.misc)
  • Re: removing value within a string
    ... parenthesis "(" in the string and extract everything to the right of that. ... This formula is simply replacing the 3 characters from the right with the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: removing value within a string
    ... of the opening parenthesis "(" in the string and extract ... This formula is simply replacing the 3 characters from the right with ...
    (microsoft.public.excel.worksheet.functions)
  • Re: removing value within a string
    ... of the opening parenthesis "(" in the string and extract ... This formula is simply replacing the 3 characters from the right with ... TRAD EC JEANS | CL11941033 ...
    (microsoft.public.excel.worksheet.functions)
Loading