Substring formula to work with string value has numeric

  • Thread starter Thread starter C Chang
  • Start date Start date
C

C Chang

I am a casual user of excel. I tried to use subtring formula, LEFT,
MID and RIGHT formula to work with the columns value having number in
it, such as 20EA or 5430-24-4444. I had tried format both to either
NUMBER or TEXT, but none of it worked with the substring formula. The
cell with formula turn out as the formula string itself. How can I
let the formula wotk? I have excel 2000 on windows 2000. I have
sought on-line without success. Can anyone give me a tip? Thanks.

C Chang
 
I am a casual user of excel. I tried to use subtring formula, LEFT,
MID and RIGHT formula to work with the columns value having number in
it, such as 20EA or 5430-24-4444. I had tried format both to either
NUMBER or TEXT, but none of it worked with the substring formula. The
cell with formula turn out as the formula string itself. How can I
let the formula wotk? I have excel 2000 on windows 2000. I have
sought on-line without success. Can anyone give me a tip? Thanks.

C Chang
Click to expand...

Post your actual formula and sample data; the result you get; and the result
you expect.


--ron
 
Ron said:
Post your actual formula and sample data; the result you get; and the result
you expect.


--ron
Click to expand...
I have a column with value as: 20EA
I used =LEFT(B2, 2) and hoped to get 20, instead I got =LEFT(B2,2)
I used =RIGHT(B2, 2) and hoped to get EA, instead I got =RIGHT(B2,2)
and no mater what default function I used, basically I got the formula
back, NOT the result. I did format the column either to text, general
or number. None of them work.
I also have a column like: 123-456-7890, I tried with all those
substring functions and others, still I got the formula back as a string.

C Chang
 
Go to Tools/Options/Views and uncheck the box marked Formulas (or press CTRL+`
-- the key to the left of the 1 on the upper row).

If that doesn't fix it, then the problem is that the cells were formatted as
TEXT before you entered the formulas. Changing the format won't fix that.
So... select the cells, then Edit/Clear/Formats. Then used Edit/Replace:
search for = (equal sign) and replace with = (equal sign). That in effect
re-enters all of the formulas.


 
I have a column with value as: 20EA
I used =LEFT(B2, 2) and hoped to get 20, instead I got =LEFT(B2,2)
I used =RIGHT(B2, 2) and hoped to get EA, instead I got =RIGHT(B2,2)
and no mater what default function I used, basically I got the formula
back, NOT the result. I did format the column either to text, general
or number. None of them work.
I also have a column like: 123-456-7890, I tried with all those
substring functions and others, still I got the formula back as a string.
Click to expand...

Either the cells with the formulas were formatted as TEXT before you entered
the formula; or you have the option to display formulas enabled.

See:

Tools/Options/View
Window Options
Ensure that "Formulas" is DEselected.


--ron
 
Back
Top