| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing unwanted characters | Excel Discussion (Misc queries) | |||
| removing pre-set characters from comments | Excel Worksheet Functions | |||
| Removing Non-Numeric Characters | Excel Discussion (Misc queries) | |||
| Removing characters from datetime field | Excel Discussion (Misc queries) | |||
| removing some of the characters from a cell | Excel Discussion (Misc queries) | |||
Linear Mode
