How can I have a number inserted into text on an excel sheet. for example if I have the number 100 in cell A1 and I want it inserted into the following sentence in sell A2:
You are 100 years old. I want the number to be able to change automatically in this sentence when the number in A1 also changes.
Yesterday I got the solution to insert the text by using custom format. Exampe: 112233 to Ab-112233 by using "Ab-"General
But when I tried the same method to inserset the Ab on 11-1122
Like 11-1122 into Ab-11-1122 in same cell, it doesn't work.
suppsose i have 50 list of numbers in column A. I want to insert a text "AAB-" in whole list. How can I do that.
FROM:
1122
1123
1124
1125
To:
AAB-1122
AAB-1123
AAB-1124
AAB-1125
I am using an ODBC database connection to input data into an excel file. When I input the data excel stores the numeric data as text and puts a ' in front of the number. There is an exclamation mark beside the cell which allows me to convert the data back to numeric format. I need excel to do this conversion for me automatically when new data is added to the file.
View 9 Replies View RelatedI want to set up a table that automatically adds rows, ideally to the top... The way I was guessing to do it would be to alter rows in the table to have a variable... My variable is 'acctstotal' anyway I figured there would be a way to edit the rows to acctstotal+ 1 or somthing along those lines but I havent been able to figure it out
btw acctstotal is a variable that simply counts the number of nonblank lines that are in the first column of the table which tells me the total number of accounts
I have no experience programming although I'm vaguely familiar with Excel offering ways to automate many processes. My current situation involves me having to insert a new line whenever a there's a change of term in a row after a long sequence of repeated terms in a specific column. So, for instance, we have something like this:
View 9 Replies View RelatedI am trying to create an automated input of numbers which will only happen if text is present in a cell on the same line:
A
1Collumn 1 Column2
21 MR X
3(cell with formula: If text is present in column 2 insert (CellA2+1). Result is 3 being inserted in A3.
I have tried to combine a ISTEXT function with an IF function but to no avail.
I can arrange for a new row to be automatically added to a spreadsheet after I have sorted the currencies in order?
So basically I am trying to record a macro that sorts the column into currency order then after the currencies have been sorted I would like an extra row added so that I can enter a total field in between....
Macro that would insert multiple pictures at once into one column with two rows per page (so that only two pictures are on each page) for word?
If so, would there also be a way to make it to were photos would have a caption box underneath each photo that list them as "Photo #1: Photo #2: Photo#3:" ect...
I know this is quite a task to achieve but I'm still relatively new to Macros and VBA and have a ton of pictures to integrate into Word for work.
In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:
Sat26/04/2008gone off sickFri25/04/2008Thu24/04/2008Wed23/04/2008Tue22/04/2008Mon21/04/2008Sun20/04/2008Sat19/04/2008Fri18/04/2008days holidayThu17/04/2008Wed16/04/2008Tue15/04/2008
I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.
I'm using Excel 2007.
I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
I have a column of an undefined number of rows where I need to add item numbers from 1 to however many items there are, starting from A9 downwards.
The last 3 used rows on the sheet contain signatures etc so it should not number the bottom 3 rows.
pretty sure its fairly simple code but i dont have anything similar from previous files that i can re-use to do this :p
just needs a simple
count how many rows are blank from A9 downwards (to say A200)
for num=1 to count do
Cell range(A(9+num) = num
end
i just dont know the code well enough to write it and make it work :p
i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet
i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.
Numbers with Dr as suffix denote positive numbers
and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.
the following issue:
I have a spreadsheet of questionnaire responses which range from 1-7
For example:
Respondent Q1
1 4
2 3
3 7
4 6
So each row is a new respondent and each column is their response from the scale.
What I need to do is code the responses into a different form. I need them to be represented as follows:
Respondent Answer1 Answer2 Answer3 Answer4 Answer 5 Answer 6 Answer 7
1 0 0 0 1 0 0 0
2 0 0 1 0 0 0 0
So that each number then represents the place on the scale from which it was chosen.
I tried recording a macro but I think this requires something a lot more complex.
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String
Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
I have pulled a report from a website. The website only allows a certain number of characters. For instance it might go to john.smith@, dave.bird@, tom.jones@... this has been pulled into an excel sheet. I want to add the domain at the end of the email address so it would become
john.smith@whatever.com
dave.bird@whatever.com
tom.jones@whatever.com
But I have a list of 2000 usernames and I don't want to go line by line inserting whatever.com. Is there a way to automatically do this?
I have data (from a very large spreadsheet) that has data as shown below in various columns. The data should be as in col A but there is a lot of it in different formats as in B. Is there any way to change the data from B into a new column say E where I can get the data into a leading number of 3 digits, the text with 1 or 2 letters and the remainder into 4 digits with the final text subscript if it is there?
Bill
A B
008D001308D13
008D000608D6
008D009208D92
008EJ00028 EJ 2
008EJ000308EJ3
008EJ0004A8EJ04A
008EJ0004B08-EJ-4B
095D006195D61
I'd like to insert a formula into a cell based on selections made on a userform. The problem is, the formula contains quotes, and in VBA, when I do this:
View 8 Replies View Relatedim on a spreadsheet that im plugging into falconview, a map viewing program-on the spreadsheet i have coordinates, but not directions (N,W,S,E)-how can i enter in say a "W" before each coord without manually plugging it in- i would greatly appreciate it-its for OTJ military work
View 11 Replies View RelatedI have text from subjects in rows, and I need to fill out the empty spaces with the specific text. For example my data looks like this:
Name1
Name2
Name3
Name4
Name5
Name6
I need it to fill out the empty cells below each name, with the top name so it looks like this:
Name1
Name1
Name1
Name2
Name2
Name3
[Code] ......
For example, one can see that Name3 has no empty cells below, hence it should just move on to the next (name4) etc.
If I have a cell value (formatted general) of say ... OneOne .... somewhere in row 2, can i insert a formula between the two One's that inserts the row number. The intention is to have the cell display .... One2One. Conceptually the cell would read One=row()One. If you know what I mean.
View 6 Replies View RelatedIs there any function or methodology to insert text in mid string? ex. I would like to insert a string after the dashes below.
0506-40365503970
0506-40365752746
0506-40365994154
I want to add (same) text to the end of a whole bunch of cells. What would be the formula or function in Excel 2007 for this? I want to add ":59" to the end of each of these:
manages
blankdisk
dentist4u
desktopsupportservices
oznc
5st
divinehope
kcdm
pokeroyunlari
hdgallery
I am trying to write a macro that will allow me to copy the text of a cell, and then insert text around that previously copied string. I am having a problem adding the copied text into my formula. Here is my code below:
Sub Bid()
'
Selection.Copy
ActiveCell.Value = "inventory levels are " & ActiveCell.PasteSpecial(xlPasteValues) & " and oversold by"
ActiveCell.Offset(1, 0).Select
End Sub
I need to insert text seperated by commas into excel. Text goes something like this:
go,back
run,hide
jump,dive
etc.....
I need to insert the text so that go, run and jump are in column 1 and back, hide and dive are in row two. I used to know how to do this but I forgot.
What I need it for is an automated report that should be used for several different projects. Each projekt have events on different dates, and I only want the report to show a specific date if there is some text (i.e. event) on that date. As it is now, it's a looong report with several blanks with only a date showing. So it should insert a row (the tricky part) + the date + the text if condition is met, and do nothing if they are not. The script should add and remove rows and text depending on what project the information is taken from.
The data it should grab is in this form (many more data inputs though..):
Date Date Date
TEXT
And the report:
Date:
Date: TEXT
Date:
and should be like:
Date: TEXT
Is it possible to take text from 2 different cell and inserting into one cell?
For example:
Cell A1 reads 'John' and cell A2 reads 'Smith' can I make cell A3 read 'John Smith' by taking those two bits of information?
VBA for updating the name of the file in Text files.
I have multiple Text files in a location; I need a VBA which can automatically update file name to these Text files.
I have attached the simple formats which i needed.
Before.txt - Original File.
After.txt - After updating the File name.
I am working on an online archive database and I need to insert "<!--More-->" (without the quotes) after 80 words in a number of cells populated with 500+ word stories. I browsed here for a while to no avail.
View 9 Replies View Related