| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing text between hyphens and removing the hyphens
I am a bit new with regards to excel but If have a column of data that has
values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
Select the range to fix
Edit|Replace what: -bRRb- (b represents the space character with: (leave blank) replace all sonofroy wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
=LEFT(A1, 4) & MID(A1, FIND("-", A1, 6)+1, 256)
Where your text is in cell A1 -- HTH... Jim Thomlinson "sonofroy" wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
This just came up but what if I want to put back one hyphen to look like this?
9999 - AAAAAAAAAAAAAAA "Jim Thomlinson" wrote: =LEFT(A1, 4) & MID(A1, FIND("-", A1, 6)+1, 256) Where your text is in cell A1 -- HTH... Jim Thomlinson "sonofroy" wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
Did you try -1 instead of +1 ?
-- David Biddulph "sonofroy" wrote in message ... This just came up but what if I want to put back one hyphen to look like this? 9999 - AAAAAAAAAAAAAAA "Jim Thomlinson" wrote: =LEFT(A1, 4) & MID(A1, FIND("-", A1, 6)+1, 256) Where your text is in cell A1 -- HTH... Jim Thomlinson "sonofroy" wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
That worked Perfectly Thank you!
"David Biddulph" wrote: Did you try -1 instead of +1 ? -- David Biddulph "sonofroy" wrote in message ... This just came up but what if I want to put back one hyphen to look like this? 9999 - AAAAAAAAAAAAAAA "Jim Thomlinson" wrote: =LEFT(A1, 4) & MID(A1, FIND("-", A1, 6)+1, 256) Where your text is in cell A1 -- HTH... Jim Thomlinson "sonofroy" wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
Perhaps something like this:
=LEFT(A2,4)&MID(A2,FIND("-",A2,6)+1,999) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "sonofroy" wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
If the 9's are fixed in number then all you need to do is to remove a fixed
length prefix: =MID(A1,11,256) This assumes the R's, the dashes, and the spaces are fixed in number and position. -- Gary''s Student - gsnu200909 "sonofroy" wrote: I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text between hyphens and removing the hyphens
=SUBSTITUTE(A1,"- RR -","")
-- David Biddulph "sonofroy" wrote in message ... I am a bit new with regards to excel but If have a column of data that has values in this format (note the placement of the hyphens) 9999- RR - AAAAAAAAAA The length of 9's are constant at 4 The length of A's can change between cells I would like to remove the R's and hypens to have this: 9999 AAAAAAAAAAA Any suggestions? Thanks for your help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing Hyphens in phone list??? | Excel Discussion (Misc queries) | |||
| Macro For Removing Hyphens | Excel Discussion (Misc queries) | |||
| Automatically removing hyphens from ID# or SSN. | Excel Discussion (Misc queries) | |||
| Remove hyphens in text | Excel Worksheet Functions | |||
| Does XL2K have "hard hyphens" (non-break hyphens)? | Excel Discussion (Misc queries) | |||
Hybrid Mode
