| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I delete a space/character from a number of fields?
I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to
get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
Why not just select the all the cells and use Excel's Edit/Replace to
replace the hyphens with the empty string (put a hyphen in the "Find what" field and leave the "Replace with" field empty)? -- Rick (MVP - Excel) "BABs" wrote in message ... I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
There is a very easy way without using code. I will assume you are using
Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
If you need code you can use this. Just change the range to fit your
application. Hope this helps! If so, let me know, click "YES" below. Sub ReplaceHyphens() Dim MyRange As Range Set MyRange = Range("A:A") MyRange.Replace What:="-", Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
or if you're looking for a code snippet -
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
This works but changes everything to scientific format or rounds off to the
nearest thousand. Can't figure out how to keep the hundreds, tens and ones. ???? "Ryan H" wrote: There is a very easy way without using code. I will assume you are using Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
Select your data, then right click the highlighted selection, select Format
Cells, select number, then choose which decimal place you want. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: This works but changes everything to scientific format or rounds off to the nearest thousand. Can't figure out how to keep the hundreds, tens and ones. ???? "Ryan H" wrote: There is a very easy way without using code. I will assume you are using Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
After editreplace change formatting to Number with no DP.
Your example shows 14 digits..........Excel will handle up to and including 15 digits. Gord Dibben MS Excel MVP On Wed, 17 Feb 2010 11:49:02 -0800, BABs wrote: This works but changes everything to scientific format or rounds off to the nearest thousand. Can't figure out how to keep the hundreds, tens and ones. ???? "Ryan H" wrote: There is a very easy way without using code. I will assume you are using Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
I am not using decimals. It changes 13-30-234-1000-2904 to 133023410002000,
rounding off to the thousands. I need it to keep the 2904 at the end. I've tried formatting the column as text, numbers with different formats, custom, etc. but they all seem to round and drop the 904 to 000. Any ideas? "Ryan H" wrote: Select your data, then right click the highlighted selection, select Format Cells, select number, then choose which decimal place you want. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: This works but changes everything to scientific format or rounds off to the nearest thousand. Can't figure out how to keep the hundreds, tens and ones. ???? "Ryan H" wrote: There is a very easy way without using code. I will assume you are using Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
And therein lies my problem, thank you. I was lazy with the example, my
column actually has 18 digits! Is there anyway that I can expand to 18 digits? "Gord Dibben" wrote: After editreplace change formatting to Number with no DP. Your example shows 14 digits..........Excel will handle up to and including 15 digits. Gord Dibben MS Excel MVP On Wed, 17 Feb 2010 11:49:02 -0800, BABs wrote: This works but changes everything to scientific format or rounds off to the nearest thousand. Can't figure out how to keep the hundreds, tens and ones. ???? "Ryan H" wrote: There is a very easy way without using code. I will assume you are using Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I delete a space/character from a number of fields?
I was able to use the Replace method just fine. Try formatting all the cells
to Number with decimal place set to 0. Then run the Replace method. Let me know if this helps, click "YES" below. -- Cheers, Ryan "BABs" wrote: I am not using decimals. It changes 13-30-234-1000-2904 to 133023410002000, rounding off to the thousands. I need it to keep the 2904 at the end. I've tried formatting the column as text, numbers with different formats, custom, etc. but they all seem to round and drop the 904 to 000. Any ideas? "Ryan H" wrote: Select your data, then right click the highlighted selection, select Format Cells, select number, then choose which decimal place you want. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: This works but changes everything to scientific format or rounds off to the nearest thousand. Can't figure out how to keep the hundreds, tens and ones. ???? "Ryan H" wrote: There is a very easy way without using code. I will assume you are using Excel 2007. Just highlight the column with the hyphenated numbers, click the Find & Select button in the Home tab on the Ribbon, click Replace, in the Find What field type -, and leave the Replace With field empty, then click the Replace All button. That should do it for you. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "BABs" wrote: I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| is 3rd character in a cell a space? | Excel Worksheet Functions | |||
| Can i give Space/some other character in between the cell? | Excel Worksheet Functions | |||
| How do I replace a BEL Character of 7 with a space | Excel Discussion (Misc queries) | |||
| NEED HELP-----Removing a space at the end of a string of character | Excel Discussion (Misc queries) | |||
| Detecting the space character | Excel Programming | |||
Linear Mode
