Concatenate Initials And Surnames

Apr 20, 2007

I have 2 columns of Surnames and Initials and I am trying to concatenate an initial (if required) with a surname or return a “blank” if the cell(s) is empty, in other words in cell AC20 I have a surname Smith and in AG20 an initial A which I only enter into the cell if there are identical surnames but when the cells are blank I wish to return a blank not a zero.

I have tried =IF( COUNTBLANK(AC20:AG20)=2,"",AG20&""&AC20) and =IF(AG20="",AC20,IF(AC20>"",AG20&" "&AC20)) but the first one won’t return the initial and the last one works but both return a zero when blank.

View 5 Replies


ADVERTISEMENT

Excel 2010 :: Return Just Surnames From List

Nov 6, 2013

I have the following data in column H ....

Excel 2010H2Ms L Sentinella3Mr R and Mrs A Agarwal4Dr H M Bingham and Mrs G M VersaceSheet1

I would like to return (into columns I and J) only the surnames that exist in each cell, so it would return something like this ...

Excel 2010HIJ2Ms L SentinellaSentinella3Mr R and Mrs A AgarwalAgarwal4Dr H M Bingham and Mrs G M VersaceBinghamVersaaceSheet1

Notice, there are two surnames in H4, so one of the surnames would be returned into I4 and the other one into J4.

I'm able to write formula to strip out everything and return the surname but only if there's one surname in the cell, I can't figure how to do it when there are two.

View 4 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

Adding Period(s) To Initials

May 5, 2012

I am trying to get periods added to initials.

Colum A
(Can be up to 4 caracters)
FJM
ML
B
GHTJ

What I need in colum B

F.J.M.
M.L.
B.
G.H.T.J.

What formula can I use in the B cells to get this result?

View 7 Replies View Related

Counting Initials And Dates

Nov 28, 2009

I'm new to the forums, though I've solved a couple of my problems by lurking here.
I'm currently having a problem with a worksheet I've been working on at work. I'm not very experienced in Exel and have inherited a worksheet that keeps track of the machines we do maintenance on during the year. What I have now is a spreadsheet with columns alternating between initials and date, and rows with the machine names all the way down. It looks similar to this :

Name of machine Initials date initials date initials date initials date etc.
machine 1
machine 2
machine 3
machine 4
...
machine 121

I'd like to keep this format, but we need to keep track of who does what within a certain week, which means I need to count the number of instances of an initial, then compare it with the date in the column next to it, which I don't think would be a problem with just two columns, but I'm having problems wrapping my brain around how to do so with 20-30 columns.
I'm using date ranges in some of my calculation formulas, which I think necessitates the date being in a separate column than the text.

View 9 Replies View Related

Formula To Create Name Initials Where Last Name Listed First

Aug 3, 2014

I am looking to create a formula to get the initials of a name, where the the last name is listed first, followed by a comma.
Now, the names could be full names or initials.

So, it could be something like: Smith, John R - result would be JRSSmith, John Robert - result would be JRSSmith, John Robert Michael - result would be JRMS There were similar formulas that I found here, but these only got the last letter of the last name - in this case the following formulas would have resulted (with the above examples) in: JTS or the last example with Michael, JLS.

=UPPER(MID(A1,FIND(" ",SUBSTITUTE(A1," ",REPT(" ",2)))+1,1)&RIGHT(A1)&LEFT(A1))
=UPPER(MID(A1,FIND(" ",A1)+1,1)&RIGHT(A1)&LEFT(A1))

Note: While I am using Excel 2010, there could be users that are still using 2003.

View 5 Replies View Related

Randomly Pick 1 Set Of Initials From List

Apr 22, 2012

cell a1 is blank
cell a2= "sb"
cell a3= "db"
cell a4 is blank
cell a5= "rm"
cell a6= "kw"

I want to randomly pick 1 set of initials from the 4 listed in the 6 cells and put the result in cell a7.

View 1 Replies View Related

Deleting Middle Initials In A Spreadsheet

Sep 8, 2009

I just bought a database and I would say at least 20% of the names in the database have an initial after the first name. I need to have a clean first and last name list. Is there a formula that can delete the initials? I am working on a variable data print project that uses their first name in the message so I cannot have their middle initial.

So like in one example her first name is listed as Susan F. I want it to just be Susan. Another example is T. Jones as a last name, I want it to be just Jones.

View 9 Replies View Related

Input Initials And Hit Enter To Display Full Name

Oct 4, 2009

Can anyone tell me if its possible to input some ones initials into a cell and when you hit enter, their full name is displayed instead of the initials:

ie input FB and the same cell displays Fred Bloggs?

View 6 Replies View Related

Pin / Password For Digital Signatures / Initials In Excel

May 29, 2014

I have QC checklist with a cell at the bottom for the user's initials. We have 8 users, and I would like to create a way that each user can enter their pin and have their initials come up in the cell. Ideally, there would be no way to the other users to know each other's pins or find out in the spreadsheet. Do you think it is do-able using VBA and how they would approach this?

View 1 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.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

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
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C

View 5 Replies View Related

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

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

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
one

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

Opposite Of Concatenate

Sep 29, 2006

I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.

View 3 Replies View Related

Concatenate (two Different Cells Into One)

Dec 11, 2008

I'm having a rather difficult time getting this one.

I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.

A picture is worth a thousand..

View 4 Replies View Related

Concatenate The Dropdown

Jun 19, 2009

I am trying to get 2 rows in one drop down without having to create another combined row. im not sure what formula should i use. I have attached an example.

View 7 Replies View Related

Increment Using Concatenate

Sep 2, 2009

I want to write formula in cells(many cells!) using concatenate. The desired output:

In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F

Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.

View 2 Replies View Related

Variable With Concatenate

Jan 2, 2013

I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:

1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8

I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008

Easy enough! Now adding the following is the problem:

1. Cell A2= 12345 Cell B2= 67 Cell C2= 89

I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089

Now D2 has one too many characters from C2

I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.

Is there a way to use a variable with the concatenate or am I using the wrong formula period ??

View 3 Replies View Related

Automatic Concatenate

Jul 9, 2009

A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".

View 7 Replies View Related

VBA Concatenate Formula

Jul 25, 2009

I'm trying to use a macro to write a formula within a column of data.

Here's the

View 3 Replies View Related

Concatenate 2 Column

Feb 2, 2010

i trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc

with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?

View 4 Replies View Related

CONCATENATE Not Working

Nov 2, 2006

Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.

If I enter
=CONCATENATE(A2,B2)
instead of showing the strings located in A2 and B2 it displays
=CONCATENATE(A2,B2)
in the cell.

I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.

View 9 Replies View Related

If / Concatenate Into One Cell

Nov 14, 2008

I have 6 columns:

A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2

I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).

So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")

Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".

View 4 Replies View Related

Concatenate Function..

Jan 23, 2010

What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com

View 2 Replies View Related

Concatenate Returning Zero

Dec 20, 2011

I have this code

Code:
Selection.FormulaR1C1Local = "=INDIRECT(CONCATENATE(""["",R4C26,""]Sheet1!"",RC))"

Which creates this in the excel cell:
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!",D4))

But for some reason, it returns a 0. However, when I change the code in the excel cell to this
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!D4"))

It works. But I need to be able to change D4 in the macro, so doing it that way won't work for me.

View 7 Replies View Related

How To Concatenate Columns

May 16, 2012

I have the below set of data for example,

aaabbbcccdddeeeaaabbbaaabbbcccaaa

I need the output as,

aaabbbcccdddeeeaaabbbaaabbbcccaaa

As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.

View 3 Replies View Related

How To Concatenate Two Arrays

Jul 24, 2012

Currently I am trying to concatenate two arrays stockotherarray and stockfittingsarray to create stockarraynew

Below is my code, I keep receiving a compile error.

Sub stockarraynew()
stockarraynew() = Split(Join(stockotherarray & Join(stockfittingsarray))
End Function

View 9 Replies View Related

Concatenate 3 Columns Into One

Jul 30, 2012

I'm trying to take the contents of 3 columns- A, B & C and have them form what I can use for a search string in column

D that I can use in a database I have.
Column A is first name
Column B is last name
Column C is Company Name

This is what i'm trying to get it to look like in column d:

("First Name Last Name" OR "FirstName.LastName") AND @companyname.com

View 6 Replies View Related

Concatenate Several Cells On Tab One

Apr 11, 2013

On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date. Any way to lock the date format for this idea?

View 7 Replies View Related

Concatenate A List

Mar 3, 2007

I have a list containing 3 the abbreviations of states in A1:A3 (one in each cell), and I would like to concatenate the list in cell B1 to display the list like this: "CA, TX, FL". At times, the list will only have one state (always in A1), but at other times it may have 2 or 3.

I tried: =IF(ISTEXT(A),A1&", "&A2&", "&A3,A1)

But, if A2 does not list a state then it returns "#value!"; and if A2 lists a state but A3 does not then it returns only the value in A1.

What can I do to get it to list all states listed in column A whether there are 1, 2, or 3 states listed?

View 9 Replies View Related

Concatenate Cells With % In Them

Apr 27, 2007

I have 7 consecutive cells which contain a %
e.g. A1 120%, B1 35% etc

I am trying to put them into one cell using concatenate
but am getting 1.20.3529411764705880.705882352941177

I would also like to separate them by comma,

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved