Replace Number

I have over 400 entries in a colomn which start with 0015...and then more 
numbers.

how can easily edit on mass so that they now begin with only 015 ???

cheers
0
Utf
3/4/2010 1:49:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
708 Views

Similar Articles

[PageSpeed] 39

Hi,

To get these leading zeroes there are 2 likely possibilities:-

1. The cells are formatted to show them so simply alter the format.

or

2. The numbers are really text and I have assumed this is the case.

So if the 'numbers' start in a1 put this in b1 and drag down

=RIGHT(A1,LEN(A1)-1)

Copy this new range and then select the 'old' range and
Edit|Paste special - select values - OK
delete the column with the formula in.
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Gazz_85" wrote:

> I have over 400 entries in a colomn which start with 0015...and then more 
> numbers.
> 
> how can easily edit on mass so that they now begin with only 015 ???
> 
> cheers
0
Utf
3/4/2010 1:58:02 PM
If there are no other instances of "00" you could use Find, Replace 
"00", "0"

Gazz_85 wrote:
> I have over 400 entries in a colomn which start with 0015...and then more 
> numbers.
> 
> how can easily edit on mass so that they now begin with only 015 ???
> 
> cheers

0
Bob
3/4/2010 2:06:17 PM
Reply:

Similar Artilces:

Finding two numbers simultaneously.
I select a value (1st number), then I need to find a second value located 5, 10 and 15 readings previous. The information is 90 rows long and 5 columns wide. Because these values don't always show up at 5, 10 and 15 readings apart, I have to keep looking until they do. The readings don't need to show up together in a group. I need one result for 5, then another for 10 and another for 15. I've been using conditional formatting to highlight the two numbers in the range of B2:F91 and counting out manually when the readings show up togetherin the 5, 10 and 15 spans. Readi...

Payroll Batch Number
Is it possible to obtain the batch number of a transaction after it has been posted? I have been asked to create a report for our payroll department that lists the batch number in addition to the transaction detail that is contained in UPR30300. Thank you, Jeff Majchrzak No, the batch number is not available for reporting. -- Charles Allen, MVP "Jeff Majchrzak" wrote: > Is it possible to obtain the batch number of a transaction after it has been > posted? I have been asked to create a report for our payroll department that > lists the bat...

Incrementing Numbers ?
Hello, I have a column with, e.g., two numbers in it. Say a 2 and the next one down is a 4. How do I select one or both so that I can simply just drag down and have a long column with: 2 4 6 8 10 etc. And, if there are already a lot of numbers in the column, must one delete them first for the scheme to work ? I tried a few methods, but just can't seem to get it to work. Also, couldn't find anything in the HELP menu for this. Thanks, Bob Select both cells then drag the bottom right corner down. The cursor turns into a small black cross when you hover over the bottom right corner ...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

how to get the number of days between two date formatted fields in vba for excel?
how to get the number of days between two date formatted fields in vba for excel? Hi Daniel, If they are Excel dates they represent the days and fractional days past Dec 31, 1899 if using the 1900 date system. So if you just have dates they have no fractional components and you just subtract the earlier date from the later date for the number of days. So it doesn't really matter if it is Excel or VBA same method. A1: 1998-11-28 B1: 2005-07-11 C1: =B1-A1 format c1 as number: #,##0 otherwise you will see: 1906-08-13 in VBA something like either of these, n...

Extracting the number from a text
Hi, In a cell say C3, the value is such as S9(09)V(05). the value of that cell might be in one of the following format: (1) S(9)V(5) (2) S9(09)V(05) (3) S9(9)V(05) (4) S9(09)V(5) (5) S(11) My requirement is: i need to get the numbers extracted which, is present before & after the letter 'V' & enclosed in parantheses. In some cases, the letter V may not be there. (for ex: point 5 - here, only one number is there ie.11) This extraction should happen ONLY if the cell E3 has the value "COMP-3." Thanks in Advance. Venkatesh V First number: =IF($E$3="COMP...

Page Number Watermark in Page Break Preview #2
It's making me crazy to. Does anyone have an answer? Pamela What's the question? Gord Dibben Excel MVP On Wed, 18 Feb 2004 10:16:05 -0800, Pamela <anonymous@discussions.microsoft.com> wrote: >It's making me crazy to. Does anyone have an answer? I don't understand this question? >-----Original Message----- >It's making me crazy to. Does anyone have an answer? >. > ...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Serial number in Queries
I am just wondering how to make the serial number through query, and it's not related with any data or table Means once I run query there is data will be show and I want to be serial number shows in the first of each raw. Ken Create a table that has the fields matching your query, plus an AutoNumber. Turn it into an Append query, and append the data to the table. The AutoNumber field gives you the sequence. If you need to programmatically reset the seed of the AutoNumber after you delete the data, see: http://allenbrowne.com/func-ADOX.html#ResetSeed -- Allen Browne - Microsof...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

breaking up a phone number
I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25877 View this thread: http://www.excelforum.com/showthread.php?threadid=392596 ...

Number format 01-21-10
How do I format a number to show in thousands with comma seperator. For example I would like to show 1050040 as 1,050. Thanks for your assistance. IN EXCEL 2007 take the following action:- Highlight the cell(s) you wnat to change - right hand click and select Format Cells (window of same name should launch) - Number tab - click in the box called:- Use 1000 Separator (,) Hit OK If my comments have helped please hit Yes. Thanks. "Jamal" wrote: > How do I format a number to show in thousands with comma seperator. For > example I woul...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Number of categories
I need to make a macro to change the between tick-mark labels with a value depending of the total numbers of rows, but I do not now the VB code to put the number of categories in the graph. I'm not a expert in VB coding, any help is appreciated, Thanks, jos One of my favorite programming tools is the macro recorder. Go to Tools menu > Macros > Record New Macro, carry out the steps you want to automate, stop the recorder, then examine the recorded code to see what you need to insert into your procedure. You'll probably have to clean it up a bit. - Jon ------- Jon Peltier...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Numbering Add-In #2
I have a purchase order template I created with Excel 97 which includes an automatic counter field to give each PO its unique ID number. When I try to open this template in XP, I am told that I need to install the numbering add-in into the Library. However, I cannot find this add-in amongst those available in Excel. I have had no luck trying to source it on the Microsoft website, find reference to this specific add-in in Offline or Online help etc. WHERE IS IT???? This add-in is no longer part of Office: http://support.microsoft.com/default.aspx?scid=kb;en-us;288118&Product=xlw ...

Inventory item numbers
There are quantities for existing item numbers. What is the best method to move these balances to new item numbers: 1) Buy item number modifier from Professional Tools or 2) Use decrease adjustments to reduce the existing balances to zero, then post increase adjustments to the new item numbers. Jackie, Is there any reason that you would want to keep the history of what happened under the old number vs. the new one? If so, then go with option 2. Will the new item be set up differently than the old one? If so, most likely you'll want option 2 (this will be true for most 'major...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Numbering for column
Hi, Good Day! I seldom used microsoft excel but sometimes i do used it for some simple calculation. When I just opened an excel spreadsheet, the numbering of column is in number format(1, 2, 3...) and not in alphabet format(A, B, C...) May I know how can i reset it back to alphabet? Thanks in advanced. rgds, Phoebe Tools|Options|General|Uncheck R1C1 Reference Style (this is picked up from the first workbook you open in that session. So make sure you save your workbook after the change--and make a note of how you fixed it. It can happen again if the first workbook opened has R1C1 refe...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

Group header page numbering
Hello All Access Guru out there Would like to have some guide / code / example on how to reset a page numbering to 1 of each group? ie Total pages = 6 Group Pages Site 1 1 / 1 Site 2 1 / 1 Site 3 1 / 2 Site 3 2 / 2 Site 4 1 / 1 Site 5 1 / 1 thaks in advance Hi, Try this (But it's in french) http://officesystemaccess.seneque.net/ex_pagination_groupe.htm TopJB PWYS a �crit : > Hello All Access Guru out there > > Would like to have some guide / code / example on how to reset a page > numbering to 1 of each group?...

How do I replace just a few characters?
Hello, I have an excel worksheet with a street name column. I need to rename some names from "Main Street" to "Main St". I can do it for one name easily with find and replace, but how do I make it work for an entire column of different names such as "Easy Street" and "Main Street". If I do find "* Street" and replace with "* ST" I get "* ST" for the output instead of "Easy ST" What am I missing? -- Tomsriv ------------------------------------------------------------------------ Tomsriv's Profile: http:/...