software

I am trying to come up with a formula that will remove any character
that is not a number (0,1,2,3,4,5,6,7,8,9)

I have cells that contain the number of years of employment and want to
standardize it so it is all the same.

This is an example of what I have now:

A1 - 10
A2 - 5yrs
A3 - 1Y
A4 - 15YRS
A5 - 12Yr

I want to make all the cells appear like A1...just the numbers

I am thinking something along the lines of quot;remove character if it is
not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9quot; but I am unsure who to write that.--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=503024You can use the Find and Replace feature, start by selecting your data then
go to Edit/Replace.....in the Find What box type: yrs then click on Replace,
leave the Replace with box empty and hit Replace All, repeat for any
remaining character.

HTH
JG

quot;jermsalermsquot; wrote:

gt;
gt; I am trying to come up with a formula that will remove any character
gt; that is not a number (0,1,2,3,4,5,6,7,8,9)
gt;
gt; I have cells that contain the number of years of employment and want to
gt; standardize it so it is all the same.
gt;
gt; This is an example of what I have now:
gt;
gt; A1 - 10
gt; A2 - 5yrs
gt; A3 - 1Y
gt; A4 - 15YRS
gt; A5 - 12Yr
gt;
gt; I want to make all the cells appear like A1...just the numbers
gt;
gt; I am thinking something along the lines of quot;remove character if it is
gt; not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9quot; but I am unsure who to write that.
gt;
gt;
gt; --
gt; jermsalerms
gt; ------------------------------------------------------------------------
gt; jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
gt; View this thread: www.excelforum.com/showthread...hreadid=503024
gt;
gt;

Try this:
For some value in A1 that starts with numbers and ends with text.

B1:
=LEFT(A1,MAX(ISNUMBER(--MID(A1,ROW(INDIRECT(quot;1:quot;amp;(LEN(A1)))),1))*ROW(INDIR ECT(quot;1:quot;amp;(LEN(A1))))))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Example:
A1: 12.5 years
The above formula returns 12.5

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;jermsalermsquot; wrote:

gt;
gt; I am trying to come up with a formula that will remove any character
gt; that is not a number (0,1,2,3,4,5,6,7,8,9)
gt;
gt; I have cells that contain the number of years of employment and want to
gt; standardize it so it is all the same.
gt;
gt; This is an example of what I have now:
gt;
gt; A1 - 10
gt; A2 - 5yrs
gt; A3 - 1Y
gt; A4 - 15YRS
gt; A5 - 12Yr
gt;
gt; I want to make all the cells appear like A1...just the numbers
gt;
gt; I am thinking something along the lines of quot;remove character if it is
gt; not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9quot; but I am unsure who to write that.
gt;
gt;
gt; --
gt; jermsalerms
gt; ------------------------------------------------------------------------
gt; jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
gt; View this thread: www.excelforum.com/showthread...hreadid=503024
gt;
gt;


This is a list of 900 records. I am trying to make it more automated
than having to figure out all the variables I have to remove.--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=503024
Better but is there anything that can be done with out the control shift
operation--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=503024The free Excel Add-in called ASAP Utilities, available at
www.asap-utilities.com has standard features that will do this for you quite
nicely......

Vaya con Dios,
Chuck, CABGx3
quot;jermsalermsquot; wrote:

gt;
gt; I am trying to come up with a formula that will remove any character
gt; that is not a number (0,1,2,3,4,5,6,7,8,9)
gt;
gt; I have cells that contain the number of years of employment and want to
gt; standardize it so it is all the same.
gt;
gt; This is an example of what I have now:
gt;
gt; A1 - 10
gt; A2 - 5yrs
gt; A3 - 1Y
gt; A4 - 15YRS
gt; A5 - 12Yr
gt;
gt; I want to make all the cells appear like A1...just the numbers
gt;
gt; I am thinking something along the lines of quot;remove character if it is
gt; not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9quot; but I am unsure who to write that.
gt;
gt;
gt; --
gt; jermsalerms
gt; ------------------------------------------------------------------------
gt; jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
gt; View this thread: www.excelforum.com/showthread...hreadid=503024
gt;
gt;


First: You might be getting the #NAME! error because sometimes the
ExcelTip forum adds extra spaces. There should be NO spaces in the
formula I posted:

B1:
=LEFT(A1,MAX(ISNUMBER(--MID(A1,ROW(INDIRECT(quot;1:quot;amp;(LEN(A1)))),1))*ROW(INDIR
ECT(quot;1:quot;amp;(LEN(A1))))))

Note: Commit that array formula by holding down the [Ctrl][Shift] keys
and
press [Enter].

Second, after entering that formula, you can just copy it down as many
rows as you need. That doesn't seem too inconvenient, is it?

Regards,
Ron--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: www.excelforum.com/member.php...oamp;userid=21419
View this thread: www.excelforum.com/showthread...hreadid=503024
Any idea how to use it to do this?--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=503024
I looked at ASAP but I must manually select what I want to convert.
This is a spreadsheet that auto updates every day with new data and
exports to a CRM system daily all on its own. having to go in and
manually change things would not be an option.--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: www.excelforum.com/member.php...oamp;userid=30167
View this thread: www.excelforum.com/showthread...hreadid=503024
Another option.

If all your text strings begin with quot;yquot; or quot;Yquot; then you could try
this.

Select your range, go to Datagt;Text to Columns. Choose Delimited,click
on Next. Uncheck all options but Other and type a lower case y in the
box to the right of the option. Click Next and Finish. Repeat this
but using the upper case Y this time.Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=503024