Removing unwanted characters
 

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 "remove character if it is
not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9" but I am unsure who to write that.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

You 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

"jermsalerms" wrote:


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 "remove character if it is
not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9" but I am unsure who to write that.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

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

B1:
=LEFT(A1,MAX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))*ROW(INDIR ECT("1:"&(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-Pro


"jermsalerms" wrote:


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 "remove character if it is
not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9" but I am unsure who to write that.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

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: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

Better but is there anything that can be done with out the control shift
operation


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

The 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



"jermsalerms" wrote:


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 "remove character if it is
not 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9" but I am unsure who to write that.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

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("1:"&(LEN(A1)))),1))*ROW(INDIR
ECT("1:"&(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: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

Any idea how to use it to do this?


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

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: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

Another option.

If all your text strings begin with "y" or "Y" then you could try
this.

Select your range, go to DataText 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: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

See if this works for your specific situation:

First, here are the rules:
-Since the numbers represent years, they will be between 0 and 99
-Values with begin with numbers
-They may or may not end with text
-There will be no decimals or punction in the numbers

If those rules apply, then:
B1: =IF(ISERROR(--LEFT(A1,2)),--LEFT(A1,1),--LEFT(A1,2))

Copy that NON-array formula down as far as you need it

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

A slight modification to Ron's last post.

=IF(ISBLANK(A1),"",IF(ISERROR(--LEFT(A1,2)),--LEFT(A1,1),--LEFT(A1,2)))

This way you can drag this down the entire sheet if you want and as new
data is entered in column A, it will appear in whatever column you are
using this formula in otherwise it will remain blank. This just
eliminates the #VALUE! error if you drag this down where no data is in
A yet.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

Thank you very much for your help...that last one is exactly what I was
looking for


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

I'm glad that worked for you
(although, I winced when I saw how many typos were in my post!)

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

I was just trying to figure that out


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=503024

Removing unwanted characters
 

Just glad I read it right. Ron did all the hard work.


Cheers


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=503024