Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Adding And Replace A Number In A Cell


i have a question about adding a number to a cell. This is the problem. Each day we track contacts we made in a spread sheet. how could i, lets say type 2 in a cell that already reads 42 and once a tab away from the cell it would change to 44 (add in the background 42+2=44).


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Adding Dim'd Variables: Ignoring Strings & Adding Number
I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.

I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.

View Replies!   View Related
Increasing A Value In A Cell By Adding A New Number To A Connected Cell?
First I would like to say that I am not English nor very good to explain myself so hope the title is according to the forums rules. Now to my problem

I would like to to put in for instance the number 100 in cell A and then the number should appear in cell B. I would like to remove the number in cell A without the number in cell B to dissapear. Then add for instance 50 in cell A to get the number in cell B to add up to 150 and so on. How can I do this? I would like to add that cell B is already connected to a different cell. And I am using Vista.

View Replies!   View Related
Find, Replace With And Then Replace Adjacent Cell
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.

View Replies!   View Related
Search And Replace Replace The Whole Cell
I have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information.

I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.

View Replies!   View Related
Replace Character With Number
In column N I would like a formula to add 3 columns,e.g. C and D and F. The problem is I want the symbol ~ replaced by 1.0 for calculation purposes.I need a solution in 1 column,if possible. I have made a few efforts,but just not getting there. See attached section of sheet.

View Replies!   View Related
How To Find Any Number Greater Then (x) And Replace With A Letter
how I could search for any number great then (x) and replace it a letter

For example I have an excel table with a series of weights.

lbs
6001
4560
6789
2000
5656
8879
1243

I like to replace any number greater or equal to 6001 lbs with the letters SS.

Example:

lbs
SS
4560
SS
2000
5656
SS
1243

Then I'd like do the same thing again but this time replace any number less
then or equal to 6000 lbs with the letters SS-LL .

Example

lbs
SS
SS-LL
SS
SS-LL
SS-LL
SS
SS-LL

View Replies!   View Related
Adding Number...
I have excel sheet with lot of number, I want total 7 digits in call. Some of numbers i have in 6 digits so I need to include 0 after “L” and Some of numbers is already has 7 digits.

e.g.

Numbers
L34654
L215487
L54875
L459875
L48546

Need to add “0” after “L”
L034654
L215487
L054875
L459875
L048546

View Replies!   View Related
Find And Replace A Specific Letter/number Combination
I have a column of references I wish to standardize. Contained within a general text description there is also an order-specific reference number, which is not relevant for my purposes. I wish to find all of these numbers and replace them with nothing (i.e. retain the rest of the description).

The reference numbers are always in the format "P#####/##". Unfortunately these references are in the middle of the text field, not at the start or end, so I can't use a LEFT or RIGHT formula to delete them.

Once these reference numbers have been deleted I will then be able to filter for unique records only. When I do this at the moment the filtering has no effect due to these specific reference numbers.

View Replies!   View Related
Adding A 0 To The Front Of A Number
I have a worksheet to keep track of products. I use an SKU column with a Unique Number to keep track of those items on the shelves.

When I started my project I never imagined that my database would grow as large as it has. I started my SKU numbers with 80000, never suspecting that I would get to 90000. I am now at 125700. Many items have been removed / sold so it only encompases only 15500 rows.

On the site that I sell these Items, the SKU's when displayed start with 100000 and go to 125700 where 80000 is next and goes to 99999? ( or the reverse depending on which tab I choose ) Not sure why this is but there is nothing I can do to change the way they do it so I must find a way to change my system. With all the 80000 - 99999 items numbered - changing them to 6 digit 125700+ numbers would be a huge undertaking so I would like to add a 0 in front of each 5 digit Number in my SKU Column. That way my items will show 080000- 125700 instead of starting in the middle.

I do keep the column my ascending order so it is currently formated as a Number. I do at times copy an paste or pull ranges items by SKU numbers to mark down or modify.

When I place a 0 manually in front of 80000- it disappears when I move from the Cell.

If I change it to a TEXT cell- it stays in place.

Excel help doesnt answer my dilemma-- nor does my book.

I see there are masks etc -- or is just text OK ? (as I stated - I do use an numbered order or range to identify groups of items at times )- if text is OK, what is the best way to add a 0 to the start of each 5 digit number other than individually ?

There are Gaps in my sequence so I cannot just replace the first cell with 080000 and pull down.

View Replies!   View Related
Adding Zero At The Start Of Number
I have phone numbers imported from outlook in csv format. Now these numbers are like 2782512512, i want to add Zero at the start of this number. So, it will become 02782512512. How can i do this. Find and Replace method doesn't work.

View Replies!   View Related
Adding A Transaction Number
I have a form that requests random info. All this information is saved to a worksheet. I would like to give each transaction a reference number. In order to do so I would like each time a new record is saved to add 1 to the previous reference number. Also I would like to start the transaction with a unique identifier "ie. rn1001".

View Replies!   View Related
Adding A Sequential Number
You will probably find this very easy but I am having all sorts of trouble making it work as I want to! Basically I need to do the following procedure...

1) Open an Inputbox to collect an eight digit number
2) Insert a column in A:A
3) In A1, enter a col header (URN)
4) In A2, enter the number that was collected in the inputbox
5) Enter sequential number from A2+1 to the last row

Ideally, this would be randomised, so after stage 2 do RAND(), sort, and clear contents, but if I can get the main part right I'm sure I can work that out!

View Replies!   View Related
Number Variable: Keep Adding 24 When The Value Return To 0
I have few range of data that may range from 0 to 24 and the return to "0 " after 24 and the 2nd set of value keep repeating 0 to 24, but I would like to keep adding 24 when the value return to 0 and if the number less than 5 will be adding 24 and the number from 6 to 8 will be equal to 11 and also the number from 9 to 23 will not change. I have attached an example that Colum A needed to be converted to column B (which I done it manually).

View Replies!   View Related
Adding 1 To A Number On Business Days
I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.

View Replies!   View Related
Adding A Number To Deg Min.dec
I have a column of Latitudes (12° 32.467'S) to which I wish to add 0.09'.
The result being 12° 32.557'S

View Replies!   View Related
Adding Consecutive Number To Userform Textbox
i've got a sample database (attached) with a userform for inputting of data. What i want it to do is automatically generate the next number and add it to textbox "our ref" on the userform. the number is in Col C. This is the code i've tried using the code highlighted in red which doesnt work.

View Replies!   View Related
Adding A Zero At The Beginning Of An INTL Phone Number
I have a very long list of international phone numbers in a .csv format that I need to put a zero in front of each one. We are doing an international autodialer campaign for an upcoming event and need the zeros at the beginning to pay for the call. How do you get around Excel not allowing a zero at the front of a number and the .csv format not allowing you to save certain formats? I've been searching the archived posts for a hour now and can't find anything on this!

Here is an example

1.14478E+13
1.1448E+13
1.14417E+13
1.14421E+13
1.14421E+13
1.1448E+13

View Replies!   View Related
Adding Time :: Number Of Minutes Worked Each Day
I import via copy paste into excel from a timekeeping programme the following time I have worked each day, as an example:

Mon 7h 55m
Tues 6h 30m
Wed 7h 24m
etc

Is there a method of changing this in excel to work out the number of minutes I have worked each day? The timekeeping programme does not let me alter any parameters, so h & m is what I have.

View Replies!   View Related
Adding Rows With Control Number That Only Repeat 3 Times: Formula
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.

Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.

So I need something that can take each control number that has three cycles and add their # of 0:15 together.

The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.

Ultimately I would like the results on a separate sheet.

This is only a part of the file, there are over 1000 different control numbers.

View Replies!   View Related
Adding A Colon ( : ) To Convert A Standard Number Into A Time Format
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function

View Replies!   View Related
Search And Replace: Loop To Look Up Xxx And Replace It With The Values Consecutively In Row C
I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.

View Replies!   View Related
Catching Errors When Using The Find / Replace Function / Replace All
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.

View Replies!   View Related
Find Part Of Text Within Cell And Replace From Another Cell
I need to find the "x" in range C2:C44 and replace it with a value (variable) specified in cell I2

The catch is that I then need to be ablt to change that character when I2 changes.

Coloum C
*VER
*T’x’STATUS
*ON
*OFF
*T’x’BANDS

EG

I2 - Variable = B
*T’x’STATUS will become *T’B’STATUS

I2 - Variable = A
*T’x’STATUS will become *T’A’STATUS

But " *OFF " should not change.

I can change *T’x’STATUS to *T’B’STATUS but then I can change *T’B’STATUS to *T’A’STATUS

Please see attached example file as it shows it better than I can explain it.

View Replies!   View Related
Replace Cell Content- Depending On Other Cell Data
I have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.

In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.

View Replies!   View Related
Adding Time: Adding Individual Cells Works, But SUM Doesn't
I have a column of times: e.g. 10:03:00 and I would like to add them all up.

=A1+A2 works fine.

=sum(A1:A10) does not.

View Replies!   View Related
Replace Name In Formula With Cell Reference
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.

View Replies!   View Related
Match And Replace Cell From Another Sheet
Question: I have two sheets named Sheet1 and Sheet2. Sheets looks like this:

Sheet1
A B
1 John Smith
2 Jane Doe
3 Joe Smo
4 Jackie Chan

Sheet2
A
John Smith
Joe Smo
Joe Smo
Joe Smo
Jane Doe
Bruce Lee

Here is the logic: If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value (id).


View Replies!   View Related
Search And Replace Formula For A Cell
I am looking for a formula for a cell that will do the following:
from :

robert johnson hot dog

To this
robert+johnson+hot+dog

I would like it only to change the space between the words to a + sign.

View Replies!   View Related
Replace Part Of A Cell (first Two Digits)
I am wanting to replace part of a cell ( the first two digits) with nothing - blank

eg cell contains 441298871657 and i want it to read 1298871657
I am looking for a function to do this, i have tried using the finf and replace function but if 44 appears somewhere else in the value this will also be replaced.


View Replies!   View Related
Replace One Row With Another Based On A Cell
I have a table that i would like to update. All my macro needs to do is search the entire first column of a table for a match to cell A1, there will only be 1 match, once the match is found row b's values need to replace the row thats entry in its first column matched cell A1.

View Replies!   View Related
Replace Cell Value In Entire Sheet
is it possible to replace the value of all the cells which have value =customer in the entire sheet.

for this first i will have to first search for all those columns which have value = customer and then replace them all.


View Replies!   View Related
Vba Code To Replace A Cell Value With Another If
I want to search column K for the word "test" and for each match, replace it with what ever cell value is in the same row but from column T ?

View Replies!   View Related
Find And Replace Part Of Cell
As part of a macro i do a find and replace on a range cells that replaces "/1" with nothing (""). Therefore 2/1 becomes 2, what i don't want though is if the de-nominator includes a 1 but not only 1 to keep the whole of the de-nominator, say 8/11 does not want to become 8/1.

View Replies!   View Related
Replace 2 Characters Signs In Cell With Another
QUESTION: How do I check to see if a cell contains either "" or "/" and if it does then replace it with "-"? THE REASON: I have a form which needs to be saved in a particular directory with the name "Form 1 - client name - matter name". To do this I have put =("G:UsersCComplianceFile OpeningForm 1 - "&B7&" - "&D42) in Cell A1 and then in VBA added the following macro to a button which saves the file

ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile

This works ok BUT, if the user puts "/" or "" in either B7 or D42, it won't save and throws up an error because you can't use those signs when saving a file.

View Replies!   View Related
Replace Part Of The Word In Cell
I have a worksheet - sheet 1 like below:

00001 - $2000
00002 - $3150
00004 - $2010
00001 - $1011

I would like to look up customer number (such as 00001) and replace to Peter

sheet2 like:

00001 Peter
00002 Marry
00003 John
00004 Mac

Expect Output:

Peter - $2000
Marry - $3150
Mac - $2010
Peter - $1011

View Replies!   View Related
Multiply Every Integer Less Than The Number In Cell A1 And Greater Than Zero By The Number Cell In A2..
Cell A1 = 3
Cell A2 = 4

Is there a formula to calculate
(3*4)+(2*4)+(1*4)

I need to multiply every integer less than the number in cell A1 and greater than zero by the number cell in A2. I was thinking factorial, but that's not it... Can't remember from my math days.

View Replies!   View Related
Split Cell And Insert (not Replace) The New Cells
I need to make sure that splited cells do not replace content of other cells. For example, if A1 has "SDR232, SDR634", this code will split them and put A1 to be "SDR232" and A2 to be "SDR634". BUT what it does is that it actually replaces whatever I have in A2. Is ther anyway to avoid replacing the content of A2?

Sub tst()
Dim X As Variant
X = Split(Range("A1").Value, ",")
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

View Replies!   View Related
Replace Words In Cell Matching Those In A List
Im hoping there is an Excel Formula (not VBA Code) solution to this problem.

I understand how to use the Substitute command in Excel, but I would like to be able to provide a list of words and have the Substitute command use that list to replace every occurrence within a given cell with a blank ("").

The twist to this is, that the List of Words will be Dynamic, and thus the formula will need to account for that.

NOTE: the formula should NOT replace parts of words, e.g., if the List Word is "can" and the cell to be evaluated contains "candle" the formula should NOT replace the "can" in "candle" with "". Only whole word matches should be replaced.


View Replies!   View Related
Find & Replace All Characters In Cell Like #
I need help with a formula (no VBA) that will find all the semi-colons ";" and pound signs "#" in a cell and replace them with this "<br> - ".

Example of Cell A1: ;#Red;#Green;#Blue;#

I started with this:

=REPLACE(MultChoice,SEARCH(";#",MultChoice),1,"<br> - ")

but it only replaces the first character.

View Replies!   View Related
Find 1 Character And Replace Entire Cell
how I could find 1 character and replace the entire cell. For example, it would find "<12", "<3", and "<32" based on "<" and then replaced that entire cell with "N/A".

View Replies!   View Related
Search Cells And Replace Text In That Cell
Search multiple cells for certain string and then replace the text of the entire cell with new text. For example the text may contain:

"A Chestnut Leather Satchel Binocular Case "They'll Fight Over When You're Dead" (Binocularcase-SL-CH)"

I want to search for SL-CH and once that is found I want to replace the contents of the entire cell with the following: Satchel Chestnut (SL-CH)

View Replies!   View Related
Find/replace Using Symbol For End Of Cell?
Is there a way to define the end of a cell in a find/replace? I.e. I want to find characters like this:

;;[space character][end of cell]

and replace with

[end of cell]

(that is just delete the ;;[space character] bit when it is followed by end of cell)

View Replies!   View Related
Find And Replace Cell That Are Blank With The Dash
I have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?

View Replies!   View Related
Replace Cells In Selection Same As Active Cell
I need some sort of code to check through a selection of cells in a column and make those cells "zero" whose value is equal to the first cell in the selection. e.g., let's say I have a key value entered in cell e6 = "2.99".

E6 F6 G6 H6 I6 J6
2.99 5.25 2.99 2.00 2.99 5.00

So the macro should be able to check the value in cell E6 (i.e. = 2.99) against values in cells from F6: J6 and any cell having the same value as cell E6 (2.99) should be made = 0. So, in the above example cells G6 & I6 will have "0" value after the code has been executed. I have close to 50 rows going down starting from cell E6. For example, E7 has a value which has to be checked against the range starting from F7 to J7 and so on and so forth.

View Replies!   View Related
Using VBA To Replace Data In A Specific Cell
I have a spreadsheet, where I am measuring dimensions for a cpk test. My question is, can I use vba to format the calculated cpk cell so that if it falls below a certain value it replaces that value with whatever I have specified in vba? Was wondering is this is possible.

View Replies!   View Related
Macro: Replace Multiple Cell Values
I have a datasheet, in which i would like to replace data...
for example, A1 has value of 'a.Male' , A2 has value of 'a.Baby'
and the list goes on...

What i want to do is, find all 'a.'s and replace the whole cell with '1' ...

So after replacing the cells A1 and A2 will be '1' instead of 'a.Male' / 'a.Baby'

If this is any help, i posted somethin i wrote... but it has to be repeated many times... i have a large data set.... I need somethin that will do the job with a single press of a button~

Sub Replace()
'
' Replace Macro
' Macro recorded 2006-11-30 by Jae Heon Choi
'
' Keyboard Shortcut: Ctrl+r
'
Cells.Find(What:="a.", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, MatchByte:=False, SearchFormat:=False).Activate

View Replies!   View Related
Replace Some Words In Cell With Those From Lookup Table
We have a list of typed information (see attached) in which we would like to remove some words and simply replace with a space "". In addition to this: Unfortunately, folks tend to mistype or misspell words, add extra digits, etc, and for those mistakes we would like to replace them with corrections. Therefore, for this problem, we have a Replacement Table and the Subject Data, an example of which is shown in the attachment. I have posted a slightly similar problem on Ozgrid recently:

Replace Words In Cell Matching Those In A List
and in that link, the following UDF was proposed:

Function ReplaceIfInList(rList As Range, rCell As Range, _
strReplacement As String) As String
Dim rLoop As Range

If rCell = "" Then Exit Function
For Each rLoop In rList
If ReplaceIfInList = vbNullString Then
ReplaceIfInList = Replace(rCell, rLoop, strReplacement)
Else
ReplaceIfInList = Replace(ReplaceIfInList, rLoop, strReplacement)
End If
Next rLoop
End Function

This worked great, but at the time i posted this, I did not have the foresight to anticipate the flexibility in what the replacement text would be (the above code always replaces with a ""). THIS new Post is asking the following:

Is there a way to define (or modify the above) User Defined Formula that will analyze the words typed in a cell, which are typically separated by spaces (or on accident, multiple spaces), and essentially use the Replacement Table as a reference for what word to replace with?

View Replies!   View Related
Replace Cell Text Leaving 1 Word
I am looking for a syntax or function wherin i can replace all lines in different cells containing specific string or text in it. e.g. Few cells in sheet contains " This guy is for deployment", other few cells may contain "guy is for core deployment" and other cells may have " project is specific deployment". I want to replace all such lines which contain a word "deployment" with ony one word "Deployment".

View Replies!   View Related
Adding Values In Cells (stops Adding After Row 14)
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?

The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?

View Replies!   View Related
Adding Data With Userform: Check For Duplicate Before Adding
I have a userform that I'm using to add data to a worksheet, with the following

Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")

OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.

IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved