Removing characters from cell - help req'd please

Hope someone can help me with this one....

I have text in column "A" a list of parts like the following.....

...nhg1234
..nhg1235.54
nhg3456
....nhg1253.7

Is thier anyway of removing the dots in front the part number?
I dont want the points moving after the the main part of the number.

Please help

TiA

mag()() 


0
11/25/2006 10:26:02 AM
excel 39879 articles. 2 followers. Follow

7 Replies
427 Views

Similar Articles

[PageSpeed] 45

Do you part numbers all start with nhg?

If yes, you could select column A
edit|Replace
what:  .n
with:  n
replace all

And keep hitting the replace all button until all are fixed.



"Mag()()" wrote:
> 
> Hope someone can help me with this one....
> 
> I have text in column "A" a list of parts like the following.....
> 
> ..nhg1234
> .nhg1235.54
> nhg3456
> ...nhg1253.7
> 
> Is thier anyway of removing the dots in front the part number?
> I dont want the points moving after the the main part of the number.
> 
> Please help
> 
> TiA
> 
> mag()()

-- 

Dave Peterson
0
petersod (12005)
11/25/2006 12:55:09 PM
Mag
You could use this UDF - it will work with any number

Function delLeadDots(x)
l = Len(x) \ 2
For i = 1 To l
  c = Mid(x, i, 1)
  If c = "." Then
    x = Application.WorksheetFunction.Substitute(x, ".", "", i)
    i = i - 1
  End If
Next
delLeadDots = x
End Function

Copy the function into a VB Module (ALT + F11, Insert, Module) and use like 
any formula

Regards
Peter

"Mag()()" wrote:

> Hope someone can help me with this one....
> 
> I have text in column "A" a list of parts like the following.....
> 
> ...nhg1234
> ..nhg1235.54
> nhg3456
> ....nhg1253.7
> 
> Is thier anyway of removing the dots in front the part number?
> I dont want the points moving after the the main part of the number.
> 
> Please help
> 
> TiA
> 
> mag()() 
> 
> 
> 
0
BillyLiddel (193)
11/25/2006 1:06:01 PM
The numbers begin with various letters and numbers.

I will have a with the module reply.

Thanks


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:45683D2D.21C7112@verizonXSPAM.net...
> Do you part numbers all start with nhg?
>
> If yes, you could select column A
> edit|Replace
> what:  .n
> with:  n
> replace all
>
> And keep hitting the replace all button until all are fixed.
>
>
>
> "Mag()()" wrote:
>>
>> Hope someone can help me with this one....
>>
>> I have text in column "A" a list of parts like the following.....
>>
>> ..nhg1234
>> .nhg1235.54
>> nhg3456
>> ...nhg1253.7
>>
>> Is thier anyway of removing the dots in front the part number?
>> I dont want the points moving after the the main part of the number.
>>
>> Please help
>>
>> TiA
>>
>> mag()()
>
> -- 
>
> Dave Peterson 


0
11/25/2006 2:33:17 PM
If there are no spaces in your part number, you could use a formula:

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"."," "))," ",".")



"Mag()()" wrote:
> 
> The numbers begin with various letters and numbers.
> 
> I will have a with the module reply.
> 
> Thanks
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:45683D2D.21C7112@verizonXSPAM.net...
> > Do you part numbers all start with nhg?
> >
> > If yes, you could select column A
> > edit|Replace
> > what:  .n
> > with:  n
> > replace all
> >
> > And keep hitting the replace all button until all are fixed.
> >
> >
> >
> > "Mag()()" wrote:
> >>
> >> Hope someone can help me with this one....
> >>
> >> I have text in column "A" a list of parts like the following.....
> >>
> >> ..nhg1234
> >> .nhg1235.54
> >> nhg3456
> >> ...nhg1253.7
> >>
> >> Is thier anyway of removing the dots in front the part number?
> >> I dont want the points moving after the the main part of the number.
> >>
> >> Please help
> >>
> >> TiA
> >>
> >> mag()()
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
11/25/2006 3:28:53 PM
Hi Ma

Here's my take on a possible code solution.  Stick it in a module and
with your code sheet being the active sheet, click Run

Sub test(
Dim rng As Rang
Dim c As Rang
Set rng = Range("A1:A" & Range("a65536").End(xlUp).Row
With CreateObject("vbscript.regexp"
myVar = Chr$(133
.Pattern = "^[\." & myVar & "]+
.Global = Tru
For Each c In rn
If .test(c.Value) Then c.Value = .Replace(c.Value, ""
Next 
End Wit
End Su

--
RichardScholla
-----------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=524
View this thread: http://www.officehelp.in/showthread.php?t=127021

Posted from - http://www.officehelp.i

0
11/25/2006 3:51:21 PM
Thanx Dave,
    Worked a treat.

mag()()


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:45686135.10E183F5@verizonXSPAM.net...
> If there are no spaces in your part number, you could use a formula:
>
> =SUBSTITUTE(TRIM(SUBSTITUTE(A1,"."," "))," ",".")
>
>
>
> "Mag()()" wrote:
>>
>> The numbers begin with various letters and numbers.
>>
>> I will have a with the module reply.
>>
>> Thanks
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:45683D2D.21C7112@verizonXSPAM.net...
>> > Do you part numbers all start with nhg?
>> >
>> > If yes, you could select column A
>> > edit|Replace
>> > what:  .n
>> > with:  n
>> > replace all
>> >
>> > And keep hitting the replace all button until all are fixed.
>> >
>> >
>> >
>> > "Mag()()" wrote:
>> >>
>> >> Hope someone can help me with this one....
>> >>
>> >> I have text in column "A" a list of parts like the following.....
>> >>
>> >> ..nhg1234
>> >> .nhg1235.54
>> >> nhg3456
>> >> ...nhg1253.7
>> >>
>> >> Is thier anyway of removing the dots in front the part number?
>> >> I dont want the points moving after the the main part of the number.
>> >>
>> >> Please help
>> >>
>> >> TiA
>> >>
>> >> mag()()
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
11/25/2006 7:24:02 PM
Hi Richard

very good - I had a macro as well.

Sub delLeadDs()
Dim l As Integer, i As Integer, c
Dim tmp
For Each c In Selection
  c.Select
  l = Len(c) \ 2
  tmp = c
  For i = 1 To l
    x = Mid(tmp, i, 1)
    If x = "." Then
      tmp = Application.WorksheetFunction.Substitute(tmp, ".", "", i)
      i = i - 1
    End If
  Next i
 c.Value = tmp
Next
End Sub

For interest maybe

Peter

"RichardSchollar" wrote:

> 
> Hi Mag
> 
> Here's my take on a possible code solution.  Stick it in a module and,
> with your code sheet being the active sheet, click Run.
> 
> Sub test()
> Dim rng As Range
> Dim c As Range
> Set rng = Range("A1:A" & Range("a65536").End(xlUp).Row)
> With CreateObject("vbscript.regexp")
> myVar = Chr$(133)
> ..Pattern = "^[\." & myVar & "]+"
> ..Global = True
> For Each c In rng
> If .test(c.Value) Then c.Value = .Replace(c.Value, "")
> Next c
> End With
> End Sub
> 
> 
> -- 
> RichardSchollar
> ------------------------------------------------------------------------
> RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
> View this thread: http://www.officehelp.in/showthread.php?t=1270214
> 
> Posted from - http://www.officehelp.in
> 
> 
0
BillyLiddel (193)
11/25/2006 8:04:01 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

How to remove a master category
How do you delete a master category once it's been created and no longer needed for a contact or calendar entry? ...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Help with Registration
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I've tried to register my copy of Office for Mac through the Mactopia page. I log in successfully, but then it just keeps on loading and doesn't refresh or change. Any advice? On 6/16/09 6:35 PM, in article 59b76b64.-1@webcrossing.caR9absDaxw, "theconfuzed1@officeformac.com" <theconfuzed1@officeformac.com> wrote: > I've tried to register my copy of Office for Mac through the Mactopia page. I > log in successfully, but then it just keeps on loading and doesn't refresh or > ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

HELP! Outlook POP3 problem(s)
Hello. I am so lost. I have a few e-mail accounts set up on my computer which retrieves my mail from a couple of different providers and deposits the mail into my Outlook Inbox. Up until yesterday, my mail always has worked fine. For some strange reason, my Outlook is now (Again) retrieving my messages from all of my accounts I had set up, which are all duplicates of my messages. There is now nearly 4,000 duplicate messages in my folders. I can't seem to stop the download of these already retrieved messages. To top things off, a couple of my email account login windows keep p...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Spam Filtering HELP
I recently started a new job, and discovered after day one, that I had inharited a spam mess. Now the previous admin ad installed a Symantic Spam Server Prox which in my opinion, was a complete waste of money as it does not allow for blocking IP addresses. Now here is the question; I am running Exchange 2003, and am looking at setting up the Conection Filter under Message Delivery to block messages based on IP address. The problem is that when I save the IPs to be blocked, I get a message stating that the Connection filter "has to be enabled manually through the specific SMTP virtual serv...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...