Concatenate 1st & Last Name And Omit Middle Name

Feb 1, 2008

In A3 is a surname, in B3 is a first name (and possibly multiple middle names, separated by a space). In C3 I'd like the first name ONLY and the surname

Hobbs.................Jon Peter........Jon Hobbs
Peters.................Mark..............Mark Peters
Jones..................Bob Tim Mark...Bob Jones

In some cells the format (all in the same cell) is:


View 4 Replies


Right Formula To Omit Zero?

May 1, 2012

I am using a simple =Right(A1,4) is there a way to change this so it looks in cell A1 and if the is character is a zero then only give me the 3 characters fromthe right?

so if A1 = sweden 2041 it would give me 2041
but if A1 = sweden 0411 it would give me 411.

View 2 Replies View Related

Omit Zero Values In A Chart

Apr 22, 2007

I am using Excel 2003.

I have pulled various data points from a pivot table into a summary.

My pivot table is located on a tab entitled "Pivots - All".

My summary tab is entitled "Open Summary".

My chart tab is entitled "Open Charts".

The formula within the summary cell that is pulling from the pivot equals
=GETPIVOTDATA("Active Y/N",'Pivots - All'!$A$70,"Active Y/N","Yes","Leader","Eurich","Expected Tenure",4)

I have found ways to NOT display the zero values in the summary tab, but the column chart (which is set to use value as the data labels, continues to pull the zeroes in the chart.

Is there a way around this besides using the "clear all" within the summary? I refresh this data weekly and don't want to have to keep redirecting the cells in the summary to the pivot.

View 9 Replies View Related

Omit Cell From Search

Aug 13, 2007

given code on this forum which created a "Search Box" on my spreadsheet.

The one thing that I would like to tweak however if that it finds the word/number that has been inputted in the search box itself (as well as finding the other genuine entries). Is there a way that I can search the whole sheet apart from cell C2 (The search box cell)?

Here is the existing
Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range
Dim more As Boolean

If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Target.Column = 4 And Target.Row 4 Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
End If

View 9 Replies View Related

Sum No-sequential Cells But Omit Zero

Dec 31, 2009

I have four cells that I want to sum: =SUM(D3,H3,L3,P3)

I want to EXCLUDE the cell from the sum if the preceeding cell (C3,I3,K3,O3) has a value of "0".

View 9 Replies View Related

Omit Zeros From Chart

Oct 23, 2007

I'm working on a report for work in which I need to produce sevearl charts. For each of these charts I need to omit any values that are 0 or null. Since this report is going to be run several times a month and with different values it would be pointless for me to do it by hand.

View 9 Replies View Related

Omit Worksheet From Calculation

May 2, 2008

Broadly, my workbook contains 5 worksheets. Worksheets 1, 2 and 3 contain calculations, worksheet 4 contains a summary of calculations from worksheets 1, 2 and 3. Worksheet 5 is a data table used for worksheets 1, 2 and 3.

I need the worksheets to calculate in the following order:
1. Worksheets 1 and 2 extract data from worksheet 5.
2. Worksheet 4 captures that data.
3. Worksheet 3 uses the data from worksheet 4 and extracts data from worksheet 5.
4. worksheets 1 and 2 recalculate using the calculation from worksheet 3.
5. Worksheet 4 captures the refreshed data from worksheets 1 and 2.

Is there a way, using formulas in the worksheets, to ignore worksheet 3 on the first iteration of worksheets 1 and 2, and prevent recalculation on the second iteration of worksheet 1 and 2?

In my mind I can see Excel simply recalculating over and over again, or will it stop. Is there actually a "problem" here or am I perceiving something that will not occur?

View 4 Replies View Related

Omit Abnormal Numbers In Formula

Jun 5, 2007

How to calculate the average of a set of numbers through excel formula when I want to ignore few skewed values. e.g. 1,3,2,5,90,2,4,56. I want to calculate average of the above set by ignoring the effect of two skewed numbers viz. 90 and 56.

View 3 Replies View Related

Omit Blanks From Conditional Average

Oct 22, 2007

I have a formula to calculate an average value for a reference range:

= SUMIF(DATA!B32:B61,"<># div/0")/MAX(1,COUNTIF(DATA!B32:B61,))

Because these cells are references the blanks are being treated as zeros so I am getting an inaccurate average value.

Is there a way to make it so it will treat blanks as blanks? I need the zeros to be zeros.

View 9 Replies View Related

Omit Hidden Rows When Counting

Oct 26, 2007

In a database of names I use Filter- Advanced Filter - Unique records, to hide duplicated rows. Trouble is I don't know if there were any duplicated rows when it finishes. I would like to see the totals reflect this by not including them in the Countif function.

View 6 Replies View Related

Omit Duplicates When Copying List

Apr 24, 2008

i need to lookup in a sheet for cells that start for example by "SE=", and copy them to another Sheet creating a list!
But some of this cells started by "SE =" are repeated, like:

SE = cars
SE = cars
SE = cars
SE = dogs
SE = dogs
SE = bike's

and this is what I'll need:

SE = cars
SE = dogs
SE = bike's

View 5 Replies View Related

Lookup Functions Omit Line Breaks?

Mar 3, 2013

Is there a lookup function available that keeps the line breaks in from the lookup array? As shown in the example the Vlookup omits them, I have also tried with Index/Match, but its the same story.

View 3 Replies View Related

Omit Cells In Filtered Column List?

Dec 7, 2013

I have a column that contains labels that are entered down to row 400. Below row 400 in the same column are formulas. If I do a filter on that column I get formula results in the filter list from the formula cells. Is there a way to omit the formula cells on the column that's being filtered? In other words, only list data in rows down to 400 in the filter list.

View 2 Replies View Related

Combine Values & Omit Redundant Data

May 8, 2007

how much excel can do and the amount of experts that is willing to help out in this forum.

Is there anyway to combine all the values to omit redundant data?

For e.g.

1 Apple 28
2 Pear 55
3 Orange 35
4 Pear 22
5 Pear 15
6 Orange 18
7 Apple 25

is there any VBA codings that could automatically reduced all the above data to

1 Apple 53
2 Pear 92
3 Orange 53

View 9 Replies View Related

Omit Null Values Calculating Average

Oct 12, 2007

I want to omit null values from monthly averages I'm calculating for some in consistent data. Currently, the macro I wrote reads the empty cells and I believe is viewing them as zeros. When taking the monthly averages, in some cases on parameter 'X' might be sampled for a particular date at a location, but another parameter 'Y' isn't sampled for whatever reason on that particular date at the location. Therefore, there is no value in the cell for parameter 'Y' for that particular date (the value is null). When the macro runs, it sees the blank cell for the particular missing date, but I believe it still views that as a 0 value which it includes in the average, instead of overlooking that cell b/c it is an unknown. So, for example, it might thinks there are actually six actual sampling results instead of five, and calculates the average based on six being the total instead of five which seems to misrepresent the average. (e.g., it's currently viewing 2, 2, Null, 2, 2, 2 as n=6 instead of n=5)

If possible, I'd like to keep the structure of the code as below with only the minimal modifications to address this issue.

I do have Options-->Window Options-->Zero Values de-selected....

View 9 Replies View Related

Calculate Future Date But Omit Weekends

Dec 27, 2007

I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day.

I currently have the weekends covered, but am stumped on the holidays.

(For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements:
IF today + 28 = Mon.-Fri., then give me today + 28.
IF today + 28 = Sat., then give me today + 30.
IF today + 28=Sun., then give me today + 29.

I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.

View 9 Replies View Related

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.


I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC

View 5 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :


Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

Get Rid Of Middle Initial

Jul 6, 2007

how can I do this?

Mike A Johnson
Tomas P. Carbone
Samuel L Jackson
Peter B. Lacrone

some have the period some dont.

im thinking I could just get rid of the periods first with a simple find/replace

so getting rid of just the middle initial ?

View 9 Replies View Related

Going Out Of A Loop In The Middle

May 16, 2006

I want to get out of a loop in the middle of it, with out going out from the whole procedure.

By the way, is there is possibility to send him to other location in the code instead of going out of the loop?

Do While ( Cells(counter, 5).Value = 1)
'some commands
If R(1) >= 0.83 * R(2) Then
'some commands
' here I want it to go out of the loop,
' but not to get out of the whole porcedure
End If
counter = counter + 1

View 7 Replies View Related

First Name-Last Name To Last Name-First Name Some With Middle Initials

Mar 8, 2008

An application I use at work exports names in a first name-last name format. At this point I have a list of about 315 names in this format. Is there a way I can convert these names to a last name-first name format? I need to export these data on a daily basis, so I'll need to perform this conversion every day. I did a search for this, but didn't find anything.

View 9 Replies View Related

How To Change First And Last Name When Person Has Middle Name

Apr 4, 2014

I have a worksheet with some names in a column with a persons first name then the second name and I would like to place the names in a different column I can have this to work ok with the following formula

=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))

This works ok but I have people with three names like Peter May Jones

When I have changed the name I would like to have (Jones, Peter May) but I get (May Peter Jones)

Also the colon I have after the first name gets put at the end of the name not after the last name.

View 12 Replies View Related

Remove Heading Row In The Middle

Aug 19, 2012

I have code that copies data from one tab, called "Report" - to another tab called "Master". The data in the "Report" tab is actually 2 reports. Both reports have the exact same format but are separated by 1 or 2 blank rows and a Heading row. Before I run the code, I have to delete the blank rows and middle heading row.

I was wondering if there is code that could find these rows and delete them.

The row that I delete has the following values (which are headings): Col 1, 2, 3. The populated columns are A - L

Alrt Type
Alrt Dt

View 1 Replies View Related

Capture Just Last Name In A Cell Containing First Last And Sometimes Middle

Jan 10, 2013

I want to extract just the last name from a cell that contains the full name and put just the last into a seperate cell.

WHAT I AM DOING: i copy the contents of a internal screen onto a spreadsheet that i use to check various peices of info and calculations. when i save I like to save my sheet with the last name and then acct number. The internal system screen combines the customers names into a single cell. Right now i have to type the last name into a new cell and have a simple save macro that concatenates that last name with the acct number and saves it into the appropriate folder.

The field i am pulling from is always formatted with FIRST NAME then MI (IF PROVIDED) and then LAST NAME. So when i dump the screen contents into excel A20 may be MIKE SMITH, or MIKE T SMITH. so i need something that looks backwards in the cell and stops at the first space and dumps SMITH into another cell of my choosing (B1 in this case)

View 4 Replies View Related

Find The 3 Leters Only When There In The Middle Of A Name

Nov 26, 2007

I have a spread sheet that the user enters 3 characters in B2 of a name, then the code will determine out of 35,000 rows with names in Column B how many match the 3 consecutive chracters, all other rows that dont match will hide.

My issue is, that the code seams to find the 3 leters only when there in the middle of a name, and will not identify all other simularities where the 3 letters are at the begining of the name, or at lerast not in all cases.

I adjusted the "Set tempCell = .Columns(2)." to (1) and that worked great but caused other problems.

lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
Set tempCell = .Range("B:B").Find(what:=Left(.Range("B2").Value, 3), After:=.Range("B2"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If tempCell Is Nothing Then
MsgBox "Not found"
Exit Sub
Set foundCell = tempCell
Range(.Range("B3"), foundCell.Offset(-1, 0)).EntireRow.Hidden = True

End If
Set tempCell = .Columns(2).FindNext(After:=foundCell)
If foundCell.Row >= tempCell.Row Then Exit Do
Range(foundCell.Offset(1, 0), tempCell.Offset(-1, 0)).EntireRow.Hidden = True
Set foundCell = tempCell
Range("A" & foundCell.Row + 1 & ":A" & lastrow).EntireRow.Hidden = True
End With
End Sub

View 9 Replies View Related

Getting A Date In A Middle Of A Formula

Jan 29, 2008

I'm using this formula to get a date thats on F16 on sheet2, but instead of returning the date, it returns a number.

=" 500 Euros per month, starting the "&Sheet2!F16&", for 6 months."

View 9 Replies View Related

Capitalize Names (first, Middle And Last)

Nov 15, 2006

I have a routine where I and others enter employee names. It takes whatever case is inputted, and formats it to Capitialize the first name, Middle Initital, and Lastname. However, my routine is flawed slightly and I was wondering if there was a way to fix it.

The flaw is in the names such as McSomething, or MacDonald, McMasters.. the routine will Capitalize the first and last names, put a comma and even put a period if there is a middle initial (or not if there is no middle initial), but I can't figure out how to identify if its a Mc or a Mac or any other of those surnames. The routine will force it to Mcsomething or Macdonald ... so.. any ideas ?? It won't capitalize (in this case) the S in Mcsomething.

Public Sub StandardizeName()

Dim FN, MI, NOFI, NOLI, FirstName, LastName, First, Middle, Lastn As String

'Standardizes all name inputs - due to user input error
FN = TextBox3.Value
Lastn = TextBox1.Value
MI = TextBox4.Value

View 9 Replies View Related

Extract Middle Text

Jan 10, 2007

I want to write a macro that is able to copy the 5th position to the 10th position characters from one cell to another. For example, in Cells(B1) contains: Mon Jan 8 09:00:02 2007. Then I want to copy "Jan 8" to Cells(A1). How do I do that? Also, i am not supposed to hardcode the cells. The codes should look something like that

start = InputBox....
end = InputBox...

For i = start To End
Cells(i,"A").Value = (whatever Function To copy 5th To 10th char of Cells(i,"B")).......
Next i

View 3 Replies View Related

Delete Middle (same) Worksheets In Many Workbooks

May 8, 2014

In the attachments is the original format of the workbook, but the sheets are blanco. Did this on purpose, because the info is sensitive.

Is there a method to delete the middle sheets, 'Total1999' and 'Klad1'?

Is there a VBA code do this in a lot of workbooks, the format of all these books are the same.

Plus I'll place them in one map if it's necessary.

View 13 Replies View Related

Place Currency And Number In Middle

Feb 2, 2009

I have a problem here with the currency style, when I put the number in the cell and clicked the currency style button, the currency is placed in the left side and the number is in right side. How can I make it both in the middle?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved