Formulas To Convert Personal Names To Different Formats.
Jan 26, 2008
I have a names in column A starting with row 7 as follows:
Walden, Douglas E
Haden, Michael
Wilson, Matt David
I need help with coming up with 3 formulas:
(1) That returns the First, Middle Initial, & Last Name in column P of the same row:
Cell P7 Douglas E Walden
Cell P8 Michael Haden
Cell P9 Matt David Wilson
(2) That returns the First & Middle Initial in colum Q of the same row:
Cell Q7 Douglas E
Cell Q8 Michael
Cell Q9 Matt D
However, I am so new at this, the simpler the formula the better because I want to try & understand it, not just learn it.
View 11 Replies
ADVERTISEMENT
Sep 21, 2005
I have 2 reports that I want to merge which are exported from different applications. The problem is that the names appear in a different format and I can't work out how to match them up. The fact that not everyone appears on every report I have solved by using lookup as in the attached workbook. What I need to do is use a formula that will recognise the two different name formats and match them up. i.e. Campion, Ben P. and Ben Campion. See attachment.
View 6 Replies
View Related
Apr 25, 2006
I have a table with 15 columns and 10 rows (initially all cells are blank but with formulas and formatted) and I want a way to add automatically a new row in my table each time something is entered on first cell in each row. For example if I enter something in cell A1 (first cell in my table) then automatically add 11th row and if I delete the value in cell A1 it is ok to leave the 11th row there but if I I go on and enter a vlaue in A2 then add row 12th. Of course I want the new rows to have the same formats and formulas as the previous ones.
View 9 Replies
View Related
Jan 14, 2013
I am constantly given the task of comparing to list of names that come from different sources. One source(SourceA) provides the names in a two Cols as first name and last name. Luckily Source A usually just has first and last name list but sometimes they have a third name or middle initial included and it could be in the first Col or the second no real pattern. The second source(SourceB) provides their list as a single Col and it has every given name for the person including suffixes and prefixes. A couple of examples of Source B names would be-"John Henry David" or "Dr. Billy Bob Adams Barone Beard IV".
I have spent countless hours and days probably weeks on some list as there are anywhere between 20-50,000 names per list I am sent. I am hoping there is a better solution to compare these names. As of right now the only thing I can do right now is manually trim the data from SourceB down to a format like "John David" and "Billy Beard" and then combining the Cols from Source A into the same format and then comparing them.
View 3 Replies
View Related
Nov 17, 2008
I've got two pieces of code.
1. cuts+pastes all formulas and formats to another location.
2. cuts+pastes required formulas but not cell formats to another location.
The s/s is over 330,000 rows deep which makes manual changes impossible.
I attach small s/s containing both codes.
What I want to do is copy+paste formulas+formats from cells having a number in the cell below to cells 4 columns along and two rows down. The s/s illustrates this.
View 11 Replies
View Related
Jan 1, 2009
I have a spreadsheet with customised formats and formulas and the problem is that when add a new row I have to go through and manually copy all the data to the new row and as there are many columns in the spreadsheet this takes forever.
Is there anyway I could automatically copy all the formats/formulas when I insert a row?
PS: I do not want to copy any data into the cell only the formatting and formulas
View 10 Replies
View Related
Oct 10, 2008
I have a column of about 1000 records. In each field is a long integer, that has been obtained through a concatenation formula. I have copied the results to a new column and used "paste special, values" to do so. I now have the results in a new column, but the cell format is text and is giving me an error. Even if I pre-format the cells that I will paste into to be a number they do not stay as a number after being pasted into, they still have an error.
I need to run a vlookup on these results but because of the error in the cells the vlookup wont work until they have been changed to a number format. I am able to change the format of the cells to number, no deciaml places, one by one. I have about 1000 records and need to be able to change the cells to number format in one go, as it is too time consuming otherwise.
If I change the format of the cells all together in one go to a number, they do not change unless you double click in them and press enter one by one. Is there a way to change the cell format for all the cells in one go without this laborious process?
View 3 Replies
View Related
Apr 28, 2007
The problem: I am getting values when doing a pastespecial for formulas and formats.
Manually copying and doing a pastespecial with the mouse gives me the correct results. Below is code I got from the forum, "here", and am running in a test workbook.
Option Explicit
Sub Test2()
'
Dim rSource As Excel. Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("A1:C1")
Set rDestination = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0)
rSource.Copy
rDestination.Select
Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
valKill:
Set rSource = Nothing
Set rDestination = Nothing
Exit Sub
End Sub
Does anyone know why I am getting values? What do I need to do to make this paste only the formats and formulas?
View 9 Replies
View Related
Jan 11, 2014
I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
Example :
week 1 hours
week 2 hours
total
[Code].....
View 2 Replies
View Related
Nov 15, 2008
I have a spreadsheet that I have people fill in. A few columns have VLOOKUPS. I want to allow people to insert lines in this spreadsheet and have the formula automatically copy in the new line. It seems to already have that behavior for formatting and conditional formatting. Is there a way to also have that for formulas when inserting lines?
Name City Assigned Salesman
-----------------------------------------------------
john Mountain View (formula using VLOOKUP)
cindy Palo Alto (formula using VLOOKUP)
ronnie sunnyvale (formula using VLOOKUP)
---------------------------------------------------
Insert line between john & cindy. Formula doesn't copy.
I ask them to copy the formula but they often forget. If I can make it a little more robust,
View 11 Replies
View Related
Sep 15, 2014
I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.
View 8 Replies
View Related
Dec 18, 2008
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
View 3 Replies
View Related
Oct 2, 2013
I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.
I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:
* Maintain tab names
* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)
* Keep the file name the same except adding "- FINAL" to the end
View 3 Replies
View Related
Aug 27, 2009
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
View 9 Replies
View Related
Apr 24, 2006
I have a very large table with 7000 rows and each time I open the file it takes excel about 1 minute to open it.Initially all cells in the rows are empty. Each row in the table has the same formats and formulas(eg. in J106 I have =IF(C106=1,1,"") and in J107 I have =IF(C107=1,1,"")).So the excel allocates all the memory and does the formatting and computations beforehand. How can I add new rows(preserving the formats and formulas of previous ones) dinammycally and automated? An example will be if I start enter data in a row add another row at the end of the table or a button that when I press it adds lets say 100 new rows in the table. Below is the table, the rows in the table to which I add data start form C15 to C7014.
View 3 Replies
View Related
Nov 23, 2005
I need to insert an Excel worksheet in the PACS (Picture Archiving and Communications System) in our medical imaging department. Our PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the Excel sheet to PDF and then JPEG and then insert it in the PACS. Is there a way (macro? VBA?) to program a cell (lets call it "Save as a Picture") so that when I click on that cell, the worksheet get saved as jpeg or tiff or dicom format?
View 3 Replies
View Related
Dec 12, 2007
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
View 11 Replies
View Related
Mar 27, 2013
Extract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
View 9 Replies
View Related
Aug 18, 2014
I have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name
And the other column lists the names as first name and then last name without the comma
I am trying to see if the list of names in one column exists in the other.
Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?
Name Lists (1).xlsx
View 14 Replies
View Related
Aug 25, 2009
I've got a bunch of formulas with cell references in them.
I've gone back and assigned names to those cells.
Is there a quick and easy way to update all the formulas with the cell names now that I've assigned them?
View 9 Replies
View Related
Jan 23, 2009
I have a workbook which contains 12 sheets, 1 for each month of the year. I also have over 100 sheets, 1 for each agent that covers their stats for each month of the year based on some of the cells from the monthly sheets.
I am trying to drag the formulas over but it will always just refer to January, instead of January and then next cell refers to the February tab, etc, etc.
View 9 Replies
View Related
Apr 15, 2013
I have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.
08-06-12
08-06-12
08-07-12
08-07-12
13/08/2012
13/08/2012
13/08/2012
14/08/2012
Above is just for an example, above dates are for the month of August, but as can been some cells are in MDY formate and some in DMY. Can I have formula to convert different date formats into one date format?
View 9 Replies
View Related
Mar 28, 2014
I've two columns, one being the first name and the second being the last name. Is there a way that I can convert these two columns into the correct format for the email address ? Rather than having to manually do this.
View 4 Replies
View Related
Sep 12, 2007
I have a product mix values as below. I want to convert the values into the % of total product mix.
CREATE TABLES LIKE BELOW?
----G---- ----H---- ----I---- --J--
4 Product A Product B Product C Total
5 32 73 125 230
6 14% 32% 54% 100%
View 9 Replies
View Related
Jun 12, 2008
To avoid manually updating a formula 100+ times, I am looking for a way to update cells in a column with reoccuring formulas with references to other cells that vary respectively from cell to cell. Meanwhile, there are other cells without formulas that this will not apply to in the same column. I am trying to avoid macros if possible, unless it can be easily understood/modified by someone who knows next to nothing about macros or VBA. My initial thought was to use the find/replace feature, but I don't know of any way to do this so the reference will update respectively for each cell (ie A9 then A10 etc.) in both the find and the replace fields.
ie
Find: A9="Text1"
Replace: OR(A9="Text1",A9="Text2")
Where the row number updates respectively.
Simplified example:
Existing:
=IF(A9="Text1","1","2")
[misc. blank or non-blank cell]
=IF(A10="Text1","1","2")
etc..................................
View 2 Replies
View Related
Jul 25, 2009
I need to rename some jpg's in the c: emp directory ( about 600 ! )
In Col A starting at A1 I have the following:-
DFCF0156
DFCF0157
DFCF0159
DFCF0161
DFCF0164
etc
In Col B starting at B1 I have the following:-
ID3425
ID5823
ID427
ID7272
ID8875
etc
In c: emp is have:-
DFCF0156.JPG
DFCF0157.JPG
DFCF0159.JPG
DFCF0161.JPG
DFCF0164.JPG
etc
What I want to end with in C:TEMP is:-
ID3425.JPG
ID5823.JPG
ID427.JPG
ID7272.JPG
ID8875.JPG
View 9 Replies
View Related
Sep 26, 2012
I am copying the value in a cell to another sheet's cell. I want to convert the formulas on the second sheet to reflect their value. I do this all the time within cells by using Past Special and Values however it doesn't give me that option within the Text Box
View 2 Replies
View Related
Dec 21, 2009
I have some mails in a colum and i would like to put with a formula into a cell.
For example, in column I have:
mail1@hotmail.com
mail2@hotmail.com
mail3@hotmail.com
mail4@hotmail.com
mail5@hotmail.com
mail6@hotmail.com
And into anything cell i would like to put with the coma:
View 9 Replies
View Related
Oct 19, 2006
I am trying to find a way to search for specific cell formulas (not the values they produce). For example, how could I search an Excel tab for a cell containing "= sum()" ? I want to ignore all other formulas and values. I then want to replace this formula only with its value.
View 4 Replies
View Related
May 15, 2009
I want to delete names, formulas, macros of another workbook. For example, A.xls is my codes workbook. From this file, I want to delete the names, formulas, macros (but to keep the values & formatting in tact) of another workbook (there must be a prompt for which .xls file, the names etc. to be deleted). All files are in a same folder.
View 5 Replies
View Related
