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


Advertisements:










Add A Letter Before An Existing Number In A Cell


I WANT TO INSERT A LETTER IN FRONT OF A NUMBERS THAT ALREADY EXIST IN CELLS
IN A COLUMN. SORT OF LIKE USING "FIND & REPLACE" EXCEPT THAT I DON'T HAVE
ANYTHING TO REPLACE; I JUST WANT TO INSERT A LETTER PREFIX IN FRONT OF
NUMBERS.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Add Decimal Point To Existing Number
I need to add a decimal point to a column of numbers. For example, where it says 126 needs to be changed to 1.26, 3035 changed to 30.35, 13593 to 135.93 and so on. Can this be done automatically or with a formula?

View Replies!   View Related
Data Validation Format Letter Number Letter Number Etc.
I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

View Replies!   View Related
Add New Data To Existing Cell Based On Multiple Selective Inputs?
I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.

For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2. To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class. I have attached the spreadsheet I am working with to try to make things a little clearer.

View Replies!   View Related
Add Time & Date In Current Cell Without Losing Existing Data
Trying to create a macro that will add the date & time & initials (i.e 8/26/09 2:34 PM JOD) into the current cell.

I've found plenty of macro's that will do this but it ends up deleting any existing text within the cell. I need to be able to add it in the middle of a text string.


View Replies!   View Related
Add Cells With Letter & Numbers In The Cell
I'am trying to add a row of cell that contain a letter befor the number
I need to add-up the numbers and ignore the letter


View Replies!   View Related
Number Of Letter In A Cell
I have a list of names and I need to know how many names are greater than 6 characters in length. What is the formula I need to enter?

View Replies!   View Related
Give Cell Number After Letter With Function.
I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

View Replies!   View Related
Insert A Letter Or A Number In Front Of Numbers In A Cell
Need a function that would insert a letter or a number in front of numbers in a cell for example

column A
3245

I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with

Column A
S3245

View Replies!   View Related
Fill Down But Have Column Letter In Formula Change And Not Cell Number
i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.

View Replies!   View Related
Add Cell Number To Date & Add Weeks
I want to add a numeric number eg: 4 to a date format eg: 15/08/2007 so that it calculates 4 WEEKS from 15/08/2007 and returns the CORRECT date in a date fomat itself. How do i do this through a VB code ?

View Replies!   View Related
Return Column Letter Based On The Letter In A Cell.
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View Replies!   View Related
Count Cells By Number & Add Adjacent Cell If Number Is X
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!

View Replies!   View Related
How To Add Formula To Existing Macro
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

View Replies!   View Related
Add Shortcuts To My Existing Macro
I have a routine that by clicking one button, that calls a macro, that currently opens Excel, or Word, or WordPerfect. The following macro uses a Case Statement looking at what the extension is, such as for Excel . . . xls

I have added a case statement for a shortcut . . . exe

View Replies!   View Related
Add IF Statement To EXISTING Formulas Over Specified Range
I need a script that will search through a selected range of cells and add a simple IF statement.

For simplicity, lets assume the desired range is Q36:Q40. (The range is much larger than that.)

The existing formula is:=IF($Z37="%Sales",Q$9*$Y37,IF($Z37="%BS",Q$78*$Y37,IF($Z37="%YOY",O37*(1+$Y37))))

I would like to keep all existing forumulas, just tack on an IF statement before hand that says if a certain cell (Q2) reads "CoPrep" then do nothing, otherwise use the existing formula. I envision this as =IF(Q2="CoPrep","",IF($Z37="%Sales",Q$9*$Y37,IF($Z37="%BS",Q$78*$Y37,IF($Z37="%YOY",O37*(1+$Y37)))))

FYI, the background to this problem is that I am in the process of making a financial forecasting model that allows for choices of company prepared forecasts, hence "CoPrep", or model forecasts based off of other critera, both of which can be sensitized.

View Replies!   View Related
How To Add More Data To Existing Cells Without Replacing It
need to add same data to every other existing cell in the column, but not replace the data already in it, but to add to it. I've tried to google the answer and look here, but I probably use bad search terms.

For example, I need to add "QW" after each of these lines:

data1432
data9292
data3933
data3939

so it would look like this:

data1432QW
data9292QW
data3933QW
data3939QW

I have a few thousand rows of data, so wouldn't rather not do it manually cell by cell by typing :-)

View Replies!   View Related
Add A Standard Footer To An Existing Worksheet
My company has a lengthy confidentiality footer that must be added on every worksheet of every workbook. I often receive existing worksheets where I need to add this footer. Is there a way to quickly/automatically add it without affecting the other existing page set up features (e.g. page orientation, margins, etc.)?

I've searched the forum and found something similar that was answered with a Before_Print Event - however I need to ensure this is on all worksheets, even if they are never printed.

The footer is: Confidential Use Only. Disclose and distribute only to XX employees having a legitimate business need to know. Disclosure outside of XX is prohibited without authorization.

I would like it centered in an 8 pt font with a hard return after each sentence end.

View Replies!   View Related
Add Sum To Existing Vba Module
I have an existing module that queries a SQL database and populates a worksheet using VBA. I would like to add to this module to include a sum of columns, but as this sheet is always dynamic, i am not sure how to sum this appropriately. for example, I have column B, I would like to add the rows from a certain point in the worksheet, but this is always dynamic, is there a way to accomodate for this so that I am always summing the column in the correct place?

View Replies!   View Related
Macro Code To Add More Rows To Existing Chart
I have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:

Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
Worksheets.Add
On Error Resume Next
Worksheets("ChartData").Delete..........................

View Replies!   View Related
Cell Format: Input The Number With The Letter "A" At The End
I have a Col where the cells have been formatted to enter "PR" before the numbers input into any cell in that Col. I did this by using the custom selection in the format cell. However some times i need to input the number with the letter "A" at the end. For instance PR123456A.

Now in some cells it lets me do this but in others it does not. It would end up with 123456A, loosing the PR at the front. I have checked that all is well within the custom window. Can any body offer an explanation as to why it could be doing this.

View Replies!   View Related
Trying To Import Specific Data From A Separate Sheet To Add To An Existing Table
I'm trying to set up a macro which will import data from one worksheet to a master sheet. I need it to copy the information into specific columns but not overwrite any existing information which is already in the Master Sheet, but I don't even know where to begin.

Just so you're clear on exactly what it is I'm trying to do... I have a Master Sheet which lists all of our suppliers prices, margins etc etc... However, when we use a new supplier we send them a greatly condensed version of the Master Sheet - We call it the Supplier Sheet (no big surprises there)!

When the supplier sends it back to me I have to type it all out manually which is kinda time consuming. I'd really like to set up a "push button" system which allows me to simply drag the Supplier Sheet into the workbook, add the info into the Master Sheet, then be able to delete the now useless Supplier Sheet.

(I have attached a test copy of the file - all of the columns in blue are the ones which need the data adding to).

View Replies!   View Related
Add Space To Each Uppercase Letter In Text
I import a CSV file into Excel where the column title row has column titles that are just one long text string, without any spacing between the words. For example:
CompanySiteDescription
CompanySiteExternalSystemID
IssueNumber

I would like a method (formula or macro) that would add a space-character before each uppercase letter (that's not the first letter in the string or an uppercase letter that directly follows another upper case letter). Thus:
CompanySiteDescription becomes Company Site Description
CompanySiteExternalSystemID becomes Company Site External System ID
IssueNumber becomes Issue Number


View Replies!   View Related
Add Each Character Code For Each Letter In String
For icount = 1 To LenComputername
valComputername = Asc(Mid(UserComputername, icount, 1))
Next icount

If the computer name is NAMTOK-PC Then the LenComputername is 9. Does that mean then that the valComputername is equal to 78?

View Replies!   View Related
Macro To Copy Existing Workbook X Number Of Times
Just curios if this is the most efficient way to copy a workbook x number of times.
I tried copying 77 workbooks and not sure exactly how long it took, but about 2 mintues. The original workbook is 300 KB.

View Replies!   View Related
Convert Letter To Number
Is there simple function that anyone knows of (or has written) that will convert a letter to its alpha-numeric equivalent?

For instance, A = 1, B = 2, AA = 27, etc (a = 1, b = 2, aa = 27)

View Replies!   View Related
Name Cells With Same Letter But Different Number?
Is there any way to name cells with same letter but different number?

e.g i need to name the first row A1 to A100.

View Replies!   View Related
Way To Get The Column Letter, Instead Of Number?
Is there a way to get the column Letter, instead of Number?

like in A1 Column() would equal 1 in B1 Column() would equal 2

I would like

in A1 Column() to equal A and in B1 Column() to equal B

View Replies!   View Related
Number Of Occurrences Of Each Letter
I need to figure out the number of occurrance of a letter in a word written in a cell. For Example i am writing "pattern" in a Excel cell. I want to know the marco/vba code that will give me the number of occurrance of each letter. The output should be:

p=1
a=1
t=2
e=1
r=1
n=1

View Replies!   View Related
Column Letter Instead Of Number
is there a way to make the following code return the letter of the column instead of the number? currently if the 'String' value that is in 'ColumnFind' is in column B this code returns a value of 2. i Need the 'B' for later code to work.

ColumnLetter = ColumnFind.Column

View Replies!   View Related
Formula To Add A Number To A Cell
I need a formula to add the number 1 into cell J9 when cell d9 is no longer blank.
I'm sure it's really easy- but I can't figure it out.

View Replies!   View Related
Add Same Number Of Spaces As Cell Value
I have a column with a possible value of 1 to 7. The value represents the day of the week. I would like the value to be displayed in such a way that it is on the right position in relation to other days. So day one is a 1 at the first position, day 2 will be a space and then a 2, day 3 will be 2 spaces and then a 3 etc etc.

View Replies!   View Related
Add Number According To Count Of Cell Value
Want to know what is the forumla for my case?

A B C
APPLE 3 APPLE=5
ORANGE 2 ORANGE=2
APPLE 2

I have data of column A and B. When A column is the same of a kind then add B and output the answer to C1.

View Replies!   View Related
Column() To Return A Letter Instead Of A Number
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?

View Replies!   View Related
Convert Column Letter To Number
Some bits of code I have learned use column numbers and some bits use column letters.

Can someone share a line or two that I could add to my macro that will convert the F representing column F into a 6, and vice versa, so that I can continue using my pre-existing bits?

View Replies!   View Related
Column() - Give Letter Instead Of Number?
When using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?


View Replies!   View Related
Knowing The Worksheet Number Or Letter
Without using VBA code, is there a way to display or find the worksheet number of the active worksheet you are viewing? All my sheets have names, and I have a lot of them.

When I want to loop through a set of them with code, I want to know what numbers they are beforehand.

View Replies!   View Related
Convert Column Number To Letter
I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?

eg. somefunction(1) gives me column(A) as an answer?

View Replies!   View Related
Exchange A Number For A Letter Forumla
I have a sheet which calculates payment amounts.

Column titles:
Hours | Rate of Pay | Total

In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.

What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.

H=2 x rate of pay = total

I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.

View Replies!   View Related
Sorting Number/letter Combos
I have a spreadsheet with information in columns a-x. In column A there are part numbers like: RH630-34, PH630-343, 6-255, 16-01, 72500, There are may combinations of just numbers, and numbers first letters second, and letter first number second.
All usually seperated by a hyphen. The entire spreadsheet will be sorted by Column A first.

I need to sort them so the order would be numbers first and combo with number letters next. finish product: 6-255, 16-01, 72500, PH630-343, RH630-34. Is this possible? I have seen other posts and suggesting putting spaces before the numbers. That seems to work but in the case of 6-138 and 6-1038 the 6-1038 is first

View Replies!   View Related
Check If Character Is A Letter Or A Number
I have a cell range that is passed as a String to a function, and within that function I need to extract only the Column letter. If it was just 1 letter it would be simple, but it may be 2, so does anybody know of a way of testing to see if the second character is a letter or a number?

View Replies!   View Related
Using .Column After An .Offset - Need The Letter But Get A Number
ce.Offset(, 4) = "=" & ce.Offset(, -1).Address & "*" & ce.Offset(, 4).Column & "25"

If X29 were equal to ce.Offset(,4) then the value in that cell should be set to

"=S29*X25"

Currenctly it is returning "=S29*2425"

(FYI - ce is just a variable to capture a particular range/cell that is dynamic and used within a For Next Loop)

View Replies!   View Related
Return Column Number Not Letter
How to return column number (not letter)?

View Replies!   View Related
Number Sequence: Add +1 To The Previous Cell
If I want to create a column of numbers, say 1 2 3 4 5, I can simply add +1 to the previous cell and then use "fill down" to generate my number sequence. How would one generate a column of numbers that repeat once? e.g.: 1 1 2 2 3 3 4 4 5 5, etc

View Replies!   View Related
Add Cell Number To End Of Corresponding Formula
I have a monthly report on an excel spreadsheet that I must sum two columns from the previous month for every row in the sheet. I wish to take the value from column B and add to the end of the formula in column A. For instance my column A would contain the following: "=1200+6595+2599+275"

Column B would be a single number, i.e. "3200"

I want to be able to click a button and get "=1200+6595+2599+275+3200" in column A and place a "0" in column B for every row on the sheet. I have a pretty good understanding of VBA, but I am still learning the Excel object model.

View Replies!   View Related
Look At Column For 3 Letter 3 Number Combos And Move
have thousands of rows and the cells look similiar to this:

FAKE NAME PARTNERS FTA048

some other combos could be FTB039 or BCL048 ETC

whats the best way of looking down a column
and moving any 3 letter 3 number combos to another column

so that FAKE NAME PARTNERS and FTA048 are in seperate columns

View Replies!   View Related
How To Output Column Letter (not Number) With A Formula
Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?

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
IF THEN Statements: Assign A Letter Grade To A Number
I have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.

A 4 History
C 2 Math
A 4 English
B 3 Physical Ed
D 1 Science

GPA 2.80

View Replies!   View Related
Sum(if Formula) Counts The Number Of Cell And Add
I have an array formula which reads:

{=SUM(IF(Female2!$A$2:$A$5000=$A403,IF(Female2!$C$2:$C$5000=C$402,IF(Female2!$1:$1=C$401,1,0))))}

However this formula only counts the number of cells (returns 96 for 96 cells) rather than adding the numbers in those cells to come to a total of 34.

View Replies!   View Related
Number Add To Comma Separated Data In Cell
Cell(i,1)have 3 Numbers

Each Number Not Allowed Greater Than 10

Each Number In Cell(i,1) Will Be Added 1 In Cell(i,3) And Cell(i+1,3)....

How Can I Seperate Numbers And Make Three Variables To Run Macro
A
1,3,10
2,5,9
C
2,3,10
1,4,10
3,5,9
2,6,9
2,5,10

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