remove text char's from a cell?

  • Thread starter Thread starter Dara
  • Start date Start date
D

Dara

Hi,
How do I remove characters from a cell with the following
format 'digit, digit, space, letter, letter'.

I need a function that will remove the letters and space
and return 'digit, digit' only.

I can't simply delete the characters since I have
thousands to do, please say there is a way!

Thank you for taking the time to read my inquiry,
Dara
 
Dara,

You can use a formula like

=LEFT(A1,2)

Enter this next to your existing data and copy down as far as you
need to go.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Dara,

If all of your data is of the format you mentioned
=LEFT(A1,2) will return text
=INT(LEFT(B8,2)) will return a number

Dan E
 
Dan, Thank you, Unfortunately this is only returning the
first digit, there are several and they vary in the # of
digitd before the space, any other suggestions?
 
Dara

=LEFT(B9,SEARCH(" ",B8,1)-1)
Or as an integer
=INT(LEFT(B9,SEARCH(" ",B8,1)-1))

OR

Like Ken suggested use text to columns, but choose
delimited and select space as the delimiter.

Dan E
 
A colleague just figured it; to remove letters from the
end of numbers that vary in lenght (has to be the same
letters) from a bunch of cells; highlight them, hit find
and replace, type in the text you want to remove, and
leave the replace box blank and hit replace all; you get
the digitd only back (even of varying lenght).
Dara
-----Original Message-----
Correction:
All those cell references should be the same
ie B8 or A1 or whatever.

Dan E

"Dan E" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Dara,

it works for me, but . . .
I suspect you may have leading spaces... if I put in
leading spaces I get #value. If you use
=LEFT(B9,SEARCH(" ",B8,1)-1) and it returns
blanks, then you have leading spaces.

Dan E
 
641461 mm*mm*mm
2356 mm*mm*mm
Click to expand...

Array-enter the following formula:


=LEFT(TRIM(A1),MATCH(FALSE,ISNUMBER(-MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1)))),1)),0)-1)

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>.

The above formula will result in the numbers as a text string. If you want
them to be actual numbers, then array-enter:


=--LEFT(TRIM(A1),MATCH(FALSE,ISNUMBER(-MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1)))),1)),0)-1)




--ron
 
I suspect you may have leading spaces... if I put in
leading spaces I get #value. If you use
=LEFT(B9,SEARCH(" ",B8,1)-1) and it returns
blanks, then you have leading spaces. ...
Click to expand...
...

=LEFT(TRIM(B8,FIND(" ",B8)-1)

to retrun the digits as text, or

=--LEFT(TRIM(B8,FIND(" ",B8)-1)

to return them as numbers.
 
Back
Top