Comments on: CONCATENATE in Excel: combine text strings, cells and columns

In your Excel workbooks, the data is not always structured according to your needs. Often you may want to split the content of one cell into individual cells or do the opposite - combine data from two or more columns into a single column. Continue reading →

Comments page 3. Total comments: 264

  1. how do i concatenate below:

    1001
    1002
    1003
    1004
    1005
    1008
    1013
    1014
    1015
    1016
    1020
    1021
    1025
    1026
    1027
    1028
    1029
    1030

    to this format:

    1001 thru 1005, 1008, 1013 thru 1016, 1020, 1021, 1025 thru 1026

    thanks

    1. 1- Select the cell where you need the result.
      2- Go to formula bar and enter data range ex. =TRANSPOSE(A1:A5)&” “
      3- Based on your regional settings, you can also try =A1:A5 (instead of =TRANSPOSE(A1:A5)).
      4- Select the entire formula and press F9 (this converts the formula into values).
      5- Remove the curly brackets from both ends.
      6- Add =CONCATENATE( to the beginning of the text and end it with a round bracket).
      7- Press Enter

  2. what is the excel formula to reflect "8/1/2017" as "Aug"?

  3. Hello all,
    Is there a way concatenated results can be searchable (ctrl+find) and/or filterable?
    Thank you in advance for any tips :)

    1. Although this question was a bit more than a year ago, I too was attempting to figure this out and landed here. Excel can natively do a find of this information. With the native find window open select "Options<<" from there make sure you select Look in: "Values". The default is "Formulas" however this will not produce the results you are looking for. Hope this helps someone.

      1. thank you Aland for above info.

    2. Hello, Aland,

      sure they can :)

      As for searching, you can use our Advanced Find and Replace add-in. You can download its fully functional trial version here and see if it works as you need.

      As for the filter, you should be able to use Excel built-in one, but if for some reason it doesn't work, you can try one from our collection of Quick Tools.

      Hope they help!

  4. How would you combine values from multiple cells separated by commas only when values exist?

    Example

    A B C
    1|George Herman Ruth
    2|Jackie Robinson
    3|Mark Marche McGwire

    Row 1= Concatenate(A1,",",B1,",",C1) = George, Herman, Ruth
    Row 2 = Jackie,,Robinson
    I don't want a comma when no values exist to separate.

    1. Hi, was this question ever resolved, I have the same issue
      1 Major;Double Major
      2 Major
      3 Major;Double Major

      1. The double comma is there because the middle cell is blank. Try this:

        =IF(B2="",CONCATENATE(A2,", ",C2),CONCATENATE(A2,", ",B2,", ",C2))

  5. I have inserted a row and typed in the formula

    =CONCATENATE(J1," ",K1)

    and have combined the first and last names of my clients. How do I now place the combined data into another cell without showing the above formula in the Excel formula bar?

    Keith

    1. I copied my combined names and then did a Paste Special into a new column. I tried a few choices until the right one popped up.

  6. Hello,

    How to a concatenate this:

    A1 = "has a account balance of" and B1 = $ 2,250.38

    When I used concatenate the result always "has a account balance of 2250.38,
    the Result that I want is " has a account balance of $ 2,250.38"

    Thank you in Advance

    1. The problem is caused by the fact that your number is not entered into the cell with a $ symbol, etc. but the cell itself is formatted as currency (the actual cell content is just 2250.38 which is why the $ does not show when concatenating). The following will work:

      =CONCATENATE(A1,DOLLAR(B1,2))

  7. how to remove duplicate entries ( which is comma separated) found in single columns ?
    example :- 8,4,7,8,6,3,3,9,6
    expected output :- 3,4,6,7,8,9

    1. I found your answer but in excel sheet if you require just mail

      my presentation is T D S but helpful.

  8. Hi Svetlana,

    The () didn't work so I've uploaded a screenshot and here the link.

    https://ibb.co/jqf3Ha

    I hope it helps!

    Thanks in advance.

    Regards,
    Abhinav

    1. Hi Abhinav,

      All comments containing external links are subject to manual validation, that's why they did not get posted immediately (the only one working anti-spam technique we could think of :)

      As for the formula, the "iframe..." string is the same for all rows except for the ID from column 1, right? If so, you can split it into 2 parts, and put the parts into 2 separate cells. For example:

      Cell F1: <iframe width="560"… embed/
      Cell F2: " frameborder="0"… </iframe>

      And then, you can use the RIGHT() function to extract the ID (from A2 in this example), and concatenate the 3 parts like this:
      =F1&RIGHT(A2,LEN(A2)-FIND("/",A2, FIND("/", A2, FIND("/",A2)+1) +2))&F2

  9. Hi

    Thank you for the detailed examples.

    I've a bit complicated query though, may be because I'm new to this.

    So, I need to fill Youtube URLs in one column and the Embed codes in another.

    Is it possible that I can just fill in the URL and the embed code generates by any formula?
    For E.g.

    Column 1: https://youtu.be/ZWiPQINKvW8

    Column 2:

    Column 2 has the Embed code which has the Video ID after "embed/" that appears at the end after the "/" in Column 1.

    It is same for all the videos and I've to maintain a sheet of Thousands of them!
    So far I've been copying and pasting both from the YouTube videos but would love to know if it's possible to merge the partial text(ID) from column 1 in the middle of the text in Column 2.

    Thanks in advance!

    Regards,
    Abhinav

    1. Hi Abhinav,

      For me to understand the task better, can you please give an example of the partial text(ID) in column 2 and the merged string (desired result)?

  10. I have 4 columns of data (A - D) and each project has 13 rows.
    Column data: Name, start, finish, duration
    Project 1
    13 rows of data

    Project 2
    13 rows of data

    How do I get Concatenate or another method to read the 13 rows of data across and then down??:
    A3, B3, C3, D3,
    A4, B4, C4, D4,
    A5, B5, C5, D5

    I need all 13 rows to read to a single row left to right.
    Can you help??

    1. We feel you bruh... You can search the World Wide Web for the solution, but don't go for a page :")

  11. How do i concatenate if I only want single quotes('') on the first set of numbers but nothing on the last 2. example. This is how I want the results to look.

    Results:
    insert into #driver select'98765432',20170517,5

    Thanks for your help!

    1. Hello, Mark,
      the ASCII code for single quotes ('') is CHAR(39). Assuming that the numbers are in A1:C1, to get the desirable result, try the following formula:

      =CONCATENATE(CHAR(39), A1 & CHAR(39),",",B1,",",C1)

      Just add the references to the cells with the text to this formula and you're good to go :)

      1. Awww so cute of you for the help :")

        1. What? Are you two years old Kal? What a jerk.

        2. jesus christ, she only answered your question, no need to be condescending...

  12. When i concatenate =1&2&3&4&5 it gives 12345
    In my program i want to remove any one of the items, say 4
    and it should show 1235.
    Can any one guide me how to do it?
    Thank you

    1. concatenate =1&2&3&4&5 it gives 12345
      then integer =int (12345) above value-11110
      your answer will be there.

    2. Just minus 11110

  13. Hello,

    If there a way to Concatenate and use Right at the same time?

    Ex: 123456 to ***456

    Doing so with on one column, ID number is 123456 then ID number 2 would be ***456

    Thank You

    1. Hello, Jess,

      if your ID is in A1, then enter the following in B1 to get ***456:
      =CONCATENATE("***",RIGHT(A1,3))

  14. how to add a space between word and number (Ex:ACT222) In a single cell.
    Thank You.

    1. If the text and numbers are always in blocks of 3 and 3 (e.g. ACT222, BOB876 etc.) then (assuming data is in cell A1):

      =CONCATENATE((MID(A1,1,3))," ",MID(A1,4,3))

  15. Hi Svetlana,

    Great website. Is there vba formula that could help sort my text strings like this:

    House1
    House2
    House3
    Truck1
    Truck2
    Truck3

    Result

    House1|House2|House3
    Truck1|Truck2|Truck3

    Thank you.

    1. Hello, Preston,

      we don't build VBA formulas, but we can provide another option which you may find useful.
      If your data starts in A1, enter the next formula in B1:
      =INDIRECT(ADDRESS(ROW()*3-2,1))&" | "&INDIRECT(ADDRESS(ROW()*3-1,1))&" | "&INDIRECT(ADDRESS(ROW()*3,1))
      This will return House1|House2|House3
      and copy the formula to the row(s) below (read how do to it here) to apply for other group(s) of the data.
      Hope it helps!

  16. How can I concatenate two dates?

    Column A: 3/1/17
    Column B: 3/5/17

    What I want for Column C: 3/1/17 - 3/5/17

    Is there a formula for this? I seem to only be able to find formulae for text and not dates.

    1. Nevermind I found it :)

      =CONCATENATE(TEXT(A1,"dd/mm/yyyy")," - ",TEXT(B1,"dd/mm/yyyy"))

  17. I have two columns with dates. Most times each column has data but some do not. I want to concatenate the two date columns into a single column. Here is what I am using: =TEXT(D2, "m/d")&" - "&TEXT(E2, "m/d"). This is working perfectly except when one of the columns is blank I get this result: 10/31 - 1/0. I want the formula to return just the single date when one column cell is blank. Help?

    1. =IF(E2="",TEXT(D2, "m/d"),TEXT(D2, "m/d")&" - "&TEXT(E2, "m/d"))

  18. Hi,
    I have data in 2 column, same values repeated in both column need unique concatenate value for combination ex-
    ColumnA ColumnB
    A B =CONCATENATE(A2,"_",B2)
    B A
    C D
    D C
    Required unique concatenate A_B(for B_A also)
    Result should
    A_B
    A_B
    C_D
    C_D

    Pease help for given concatenate and vlookup formula
    Thanks

    1. =IF(B2>A2,CONCATENATE(A2,"_",B2),CONCATENATE(B2,"_",A2))

  19. Hey,

    I am using the concatenate command to put a group of things together. One of the numbers is a sum of a bunch of numbers from another page and when not formatted it has a number of digits after the decimal point (17.9476930697) In the original cell I have it formatted to only show 2 digits after the decimal point but when I run the command the formatting resets and shows every number after the digit point.

    I have multiple cells and multiple lines so just "hard inputting" the number isn't much of an option and its a document that could undergo revisions.

    Is there anyway to format numbers within a concatenate command?

    Cheers,

    1. The ROUND function can be used to change the number of decimal places being shown when you are concatenating - e.g. text in cell A1, number with 9 decimal places in B1. The following will reduce to the number part to 2 decimal places:

      =CONCATENATE(A1,ROUND(B1,2))

  20. Dear all,

    it is possible to "force" Excel in some way, to count with the string, constructed by CONCATENATE, as the function?

    For explanation - I have this data: CONCATENATE(„=“;“1″;“+“;“1″) and I need the result to see as "2" and not only as "=1+1"

    Any idea?

    Thanks for your answer in advance!!!

    1. Hi Richard,

      For this, use the embedded SUM function like this:

      =CONCATENATE("=";SUM(1+1))

      1. Hi Svetlana, yes, it count the embedded numerical example, but the result is still the string in the shape "=2". I need some solution, where the result is not a string, but the completely counted function (i.e. only "2").

        In real, I have a little bit complicated data in my sheet, "1+1" is only example for easy explanation of my problem.

        1. Richard,

          The result of the Concatenate function is always a text string. If you want to return a number, use SUM or SUMIF function, or a combination of Sum functions. It's not possible to advise an exact formula without knowing the details of your task.

  21. What do I write, if I want to have the output from referencing a large amount of cells, like =CONCATENATE(S33:S94), but I want a " " between each of the cell values?

    1. If you have too many values this would take forever. I found an easier solution. I had to merge the values of 2000 cells down a column, say column B, into one cell with a | between each value. Listing 2000 cells individually would have taken forever.

      In C2 I put the formula =CONCATENATE(B1&"|"&B2)
      Then, in C3 I put =CONCATENATE(C2&"|"&B4)
      Copy the 2nd formula down as far as you want. In a moment I ended up with cell C2000, that had 2000 values all in one cell separtaed by |.

      1. For the second formula I meant in C3 put =CONCATENATE(C2&"|"&B3)

    2. In this case you should reference each cell individually and insert spaces in between, like this:
      =CONCATENATE(S33, " ", S34, " ", S35, " ", ..., S94)

  22. A1 B1
    Aug-16 BR BANDAR

    How do I combine both of above cells (A1 & B1) into "Bandar August-16" by dropping the word "BR" ?

    1. Hi Asmiza,

      Does A1 contain a date in the mmm-dd format or text "Aug-16"? You can check this by selecting cell A1 and viewing the value in the formula bar.

      If it's a date, you can use the following formula:

      =TEXT(A1, "dd-mmmm") &RIGHT(B1,LEN(B1)-SEARCH(" ",B1)+1)

      If it's a text, then use this one:

      =A1 &RIGHT(B1,LEN(B1)-SEARCH(" ",B1)+1)

  23. hi All,

    How to convert 3-10-10-10-10001 In 310101010001. ?

    Please Help

    1. Hello Azeem,

      You can use Excel's Find and Replace feature (Ctrl+H) and replace dashes with nothing, i.e. you type a hyphen (-) in the "Find what" box, leave the "Replace with" box empty and click Replace All.

  24. I have a doubt with concatenate with time, fr example i have abc123456 in A1 and 4.00 PM in B1 so if i use the formula the time changes to 0.666667. can you help me with that.

    1. Use CONCATENATE(A1;" ";TEXT(B1;"h.mm AM/PM")

  25. Hi,
    Am trying to create a production planning sheet where I have to provide data with comma separators for the values for convenient reading.

    When I use concatenate function I cannot get the commas in the middle.

    When I use the function it return 100000 Mtrs/Day Where areas I require it as 1,00,000 Mtrs /Day.

    Is there any function to do it ?

    Please help.

    1. _ * #,##0_ ;_ * -#,##0_ ;_ * "-"_ ;_ @_

      Format cell by above using custom cell use unit Mtrs/Day in next cell
      because you wan't to use in production sheet. This is not my formulae
      but it will help you.

    2. Arun,

      I am afraid it's not possible. The display representation of cell values is controlled by the format applied to a given cell. If you had a regular number, it would be sufficient to check the "Use 1,000 separator" box in the Format Cell dialog. However, the result of the Concatenate formula is an text string, and Excel cannot insert a thousand separator there.

  26. I have the below data in singe cell,

    62 F1 11 43 50 4C 41 2D 31

    i need to split the same value in to different cell.

    can u pls some on suggest me to solve this..?

    1. Dear Nagaraj,

      Select the required cells (only in one column at a time)
      Go to Data - Text to Columns - Delimited - Next -
      Select Space as the delimiter.
      Click Finish.

      If the data has to be converted vertically, then use.
      Home - Paste Special - Values - Transpose
      Select Values and Transpose
      Click OK.

      Vijaykumar Shetye, Goa, India

  27. Hi,

    I want to format/custom the following data:

    E101-i
    E1-j
    E1-a
    E201-d

    So, I want to fixed the "E" and "-"with different numbers(up to 3 digit) and end with 1 alphabet

    1. Dear jessica,

      Go to Home - Number - Category - Custom - Type - "E"###"-d"

      If the letter changes depending on the content of some other cell, then,
      Go to Home - Conditional Formatting - New Rule - Use a Formula to determine which cells to format -
      In Format values where this formula is true, enter the formula
      =IF(A1="d",1,0)
      Select Format - Number - Custom Format
      Select the required Custom Format.

      You can create multiple Custom Formats and enter the required formulas. Change the cell references as required.

      Do let me know if any changes are needed.

      Vijaykumar Shetye, Goa, India

  28. I have a question related to the text combination with two different sheet.

    Example:
    CODE DESCRIPTION
    10001 THC
    10001 OCF
    10001 AGENCY
    10002 THC
    10002 DOC

    The result should be for another sheet
    CODE DESCRIPTION
    10001 THC,OCF,AGENCY
    10002 THC,DOC

    Can you share me how to solve this problem?

    1. Hi,

      I'm after a solution like that as well.

      Example:
      CODE DESCRIPTION
      10001 THC
      10001 OCF
      10001 AGENCY
      10002 THC
      10002 DOC

      The result should be for another sheet
      CODE DESCRIPTION
      10001 THC,OCF,AGENCY
      10002 THC,DOC

      Can you share me how to solve this problem?

      Many thanks

  29. How do I concatenate, when one of the cells I am trying to use is a formula?

    For example (simplified), cell A1 is =NOW() which is showing the month we are currently in. I then want to concatenate this with cell B1 which is just text.

    When I use write the formula =CONCATENATE(A1,B1) I get this as the result 42450.4964960648exampletext where I actually want it to say Marchexampletext

    I do not want to copy and paste the month as text, and I do not want to use VBA/macro.

    Does anyone know how to do this?

    1. Hi Liz,

      One possible way is to concatenate the function. For example:
      =CONCATENATE(TEXT(TODAY(), "mmmm"), "text")

      The result of the above formula will be "Apriltext".

      To make the output better readable, you can include some separator like "-":

      =CONCATENATE(TEXT(TODAY(), "mmmm"), "-", "text")

      Another way is to reference the original cell with your formula. But again, you need to specify the format of the text value you want to return. In this example, "mmmm" is the full name of the month:
      =CONCATENATE(TEXT(A2, "mmmm"), "text")

      Because in the internal Excel system dates and times are stored as numbers, you cannot simply concatenate cells with NOW() or TODAY() functions.

      You can learn more about Date and Time formats in this tutorial:
      https://www.ablebits.com/office-addins-blog/change-date-format-excel/

  30. Hi

    I want to concat values in 2 different cells and then copy down, but when copying down, i want to keep 1 of the cells the same.

    Example:

    =CONCATENATE(B5,D2), =CONCATENATE(B6,D2), =CONCATENATE(B7,D2), etc...

    but when i copy down, excel automatically changes it to =CONCATENATE(B6,D3), =CONCATENATE(B7,D4), etc...

    Anyone knows how I can get this to work?

    Thanks!

    1. Tammy - you can add an absolute reference around whichever field you want to be constant by simply adding two $ signs:
      Ex: from you formula above - =CONCATENATE(B5,$D$2) which you can then auto-fill the rest of the way down.

  31. I am trying to use Concatenate or & to combine contents of column items in a row. I can get that to work, but if I drag the formula down it only shows the output value of the first cell. It doesn't recognize the formula as a formula where the cells are colored, (the relative cell ID changes correctly, however.) The only thing I have found to fix this is to click on each cell with the formula and then click in the function bar then the cells in the formula "color themselves" and then the output value is correct.

    Example of What I Get:
    INPUT | FORMULA | OUTPUT
    A B C | |
    1 R T Y | =A1&B1&C1 | RTY
    2 F G H | =A2&B2&C2 | RTY
    3 V B N | =A3&B3&C3 | RTY

    Example of What I Think I Should Get:
    INPUT | FORMULA | OUTPUT
    A B C | |
    1 R T Y | =A1&B1&C1 | RTY
    2 F G H | =A2&B2&C2 | FGN
    3 V B N | =A3&B3&C3 | VBN

    1. I figured it out... These are static formulas. I changed the Calculation Option to Automatic and it fixes it.

  32. want A column value in C column if B column have yes word in google sheet ..

    for ex:

    A column --- B column ----- C column
    1 --- yes ----- 1
    2 --- -----
    3 ---- -----
    4 ---- yes ----- 4

    i try using this formula =IF(SEARCH("yes",B1), CONCATENATE(A1, " "), "")

    and it works but i want to show C column values in other sheet like in one sheet i have two sheets .. Sheet1 have these A column and B column values .. and i want to use this formula in sheet2 to get this result but its not work .. can you please help me in this and one more thing can i use it like " =IF(SEARCH("yes",B1:B), CONCATENATE(A1:A, " "), "")" i mean rang , i dont wont to run this manually every time when i insert new value in a column and in b column . rang define and it will do the rest automatic in sheet2 .. i hope you understand my question .. thanks

    1. Hi, your logical statement in the IF function does not return a boolean value which is why it seems that your calculation is not working. You should make the formula like this:

      =IF(ISNUMBER(SEARCH("yes",B1:B)), CONCATENATE(A1:A, " "), "")

  33. I want to Concatenate the numbers below, to create this format 55-19-00-0-007-00-00 but when I run the formula, this is the result that I get 55-19-0-0-7-0-0. Is there a way to keep the "00" and the "0" before the numbers after running a formula?

    A B C D E F G
    55 19 00 0 007 00 00

    1. Hi Chalo,

      Please try to change the cells format to Text or use the following formula:

      =CONCATENATE(A1, "-", B1, "-", TEXT(C1, "00"), "-", D1, "-", TEXT(E1, "000"), "-", TEXT(F1, "00"), "-", TEXT(G1, 0))

  34. I keep getting an error when trying to use this formula for concatenate and I can't figure out what's wrong. I'm trying to combine written text and also pulling in information from other cells. Please help!

    =CONCATENATE(“Blank company currently has a position available for a ” ,H1, “ traveling nurse. This assignment is for an immediate start with a reputable facility seeking an experienced RN to fill a ” ,N1, “ contract in their intensive care unit. At Blank Company our Nurses comes FIRST! We pride ourselves in the commitment to our nurses! We have a dedicated, experienced team of industry leading healthcare recruiters to support our partnered nurses with 24/7 support. Our experienced recruiters get to know you to ensure the best placements are the best fit for you! We treat you like you're part of our Blank Company family. We value your compassion, dedication and knowledge in the healthcare industry! Your career is our focus; so let Blank Company be the gateway to your future opportunities! Apply today! Blank Company an Equal Employment Opportunity Employer.”)

    1. Hi Jillian,

      Text values in formulas are limited to 255 characters.
      Please try to put the string " contact in their intensive..." in another cell and use this cell reference in your formula.

  35. Hi Svetlana,

    Can I please check with you if CONCATENATE formulas can be used with IF?
    I have a list of employees with their departments and other details. What I'm trying to do is to amalgamate all employees of each department into one cell separated by a comma. Employees names are in column B and departments' names in column D.

    Many thanks for your help.

    J

    1. Hi Jim,

      Sure, you can use CONCATENATE together with IF. However, I cannot think of any formula that handle your task. This can be done with VBA code, or you can try our Combine Rows Wizard for Excel.

      1. Hi Jim,
        Use the below formula
        =CONCATENATE(TRANSPOSE((B3:B10)&IF(LEN(B3:B10)>1,"-","")&D3:D10&IF(LEN(D4:D11)>1,"-","")))

        But, before clicking enter, select the Transpose part
        TRANSPOSE((B3:B10)&IF(LEN(B3:B10)>1,"-","")&D3:D10&IF(LEN(D4:D11)>1,"-",""))
        and click F9
        Then click Enter.

        I have use B3 to B10 and D3 to D10 as the cell references. Change them as required. In case cells are empty, the result for those cells will be blank.
        I have used "-" and "," as the text separators for the names and departments.

        Do inform me if any changes are required in the formula.

        Vijaykumar Shetye, Goa, India

  36. Hello!

    I have a list that was sent to me with addresses listed.

    A2 - Name B2 - Street B2 - City D2 - State E2 - Zipcode.

    Can I use concatenate to formulate it into one cell as following?

    Name
    Street
    City, State, Zip

    Thank You?

    1. Hi Chelsea,

      Please try to use the formula below and set the "Wrap text" option for the resulting cell (Ribbon tab Home - Alignment - Wrap Text).

      =CONCATENATE(A2, CHAR(10), B2, CHAR(10), C2, CHAR(10), D2, CHAR(10), E2)

  37. Excellent. was very helpful to me

    1. Excellant Answer.
      Thanks you son much

  38. columnA Coloumn B Example
    Name DIFFERENT Mobile nO oF same PERSON
    John 9856565132
    Jack 5616546511
    Moses 4456456466
    lee 4964895116
    Jacobs 4565515156
    John 8944988941
    Jack 4984512984
    Moses 5115619851
    lee 5985947894
    Jacobs 5158648998
    Jack 4989899999
    Moses 4944988916
    lee 8894498994
    Jacobs 9889449984
    JOHN 5498416516

    BUT I WANT THIS LIKE THAT
    SEE:
    NAME MOBILE-1 MOBILE-2 MOBILE-3
    John 9856565132 8944988941 5498416516
    Jack
    Moses
    lee
    Jacobs
    PLEASE HELP ME BY USING FORMULA

    1. Dear IMRAN AZIZ,
      Follow the below mentioned steps to get your work done.

      (1) Copy the existing data in cells B1 to C15.
      In cell A1, enter the below formula and drag it down.
      =B1&" "&COUNTIF($B$1:B1,B1)
      The data in column A will be John 1, Jack 1, ...

      (2) Copy the names in column B.
      Paste the names in cell A18.
      Select the names in cells A18 to A32.
      Go to Data - Remove duplicates and remove all the duplicate values.

      (3) In cell B18, enter the formula
      =IFERROR(VLOOKUP($A18&" "&COLUMN()-1,$A$1:$C$15,3,FALSE),"-")
      Drag this formula to the right. If a person has 10 numbers, drag it upto 10 columns to the right.
      Change the cell references as required. I have started in column 2, so the formula contains the part COLUMN()-1. If you start at column 5, then change this part to COLUMN()-4.

      Vijaykumar Shetye, Goa, India

  39. What is the process of combining text within a fourmulae called. Please name in one word

    1. joining

  40. Hey, I'm trying to use different cells to input the row and column to call on a specific cell.
    So like G4=C and G5=5 and I want them to combine to point to cell C5 and output whats in C5, Can I use this or do I need another method?

      1. Hi Sevtlana, how are you?
        I entered G4=C, G5=5, =INDIRECT(G4,G5)
        here is the result: #REF!
        in other case, #VALUE!
        Any help? : )
        - artu

        1. Dear Artu,
          The formula that has been given to you is =INDIRECT(G4&G5),
          and you have changed it to =INDIRECT(G4,G5).
          The syntax you have used is wrong. The formula will not for the way you want it to.

          Please use it correctly.

          Indirect function returns the reference specified by a text string.

          Vijaykumar Shetye, Goa, India

  41. 31101-5982071-7 RIZWAN ALI SHAH SAYED MUHAMMAD ASLAM
    PARVEZ
    Ph: 03333512564 Address : AZIZ STREET URDU
    ROAD H # 109 BAHAWALNAGAR
    1
    31101-1601721-9 MUHAMMED IJAZ MUHAMMED YAR Ph: 03027850271 Address : BASTI MAHARAN KOT
    FATEH BWN
    2
    31101-8058988-7 ABDUL QAVI KHAN ABDUL HAI Ph: 03007582490 Address : H.NO.239 STREET NO.2
    TAKVA COLONY BAHAWALNAGAR
    3
    31101-4402145-7 MUHAMMAD RIAZ JAN MUHAMMAD Ph: 03046191688 Address : BASTI DARBAR BAGH
    WAN RAMZAN LANGHA BAHAWALNGAR
    4
    31101-1653950-5 KHALID MEHMOOD MUHAMMAD YAQOOB Ph: 03007580790 Address : STREET NO.5
    FAROOQABAD SHARKI BAHAWALNAGAR
    5

    hello, i have the above mention data, i want to write in one line, in some total data contain in two lines some where in three, how i can join two or three lines in one line?

    1. Hello, Nazim,

      Most likely a macro is needed in this case. Sorry, we cannot help you with it.

  42. Can you show me how to use the CONCATENATE function and the VLOOKUP function together for a person's first and last name?

    1. Hello Stephen,

      You can put it as simply as:

      =CONCATENATE(VLOOKUP(), " ", VLOOKUP())

      Where the first vlookup gets the first name, and the second - the last name. You insert " " in between to separate the parts of a name with a space.

      A real-life formula may look similar to this:

      =CONCATENATE(VLOOKUP(E2,$A$2:$C$10,2,FALSE), " ", VLOOKUP(E2,$A$2:$C$10,3,FALSE))

      Where E2 is the lookup value (some unique identifier like security numbers, which are in column A), column B is the first name and column C is the last name.

  43. Hi

    I have
    Total time (Outgoing Calls)
    01h. 48m. 41s
    00h. 50m. 04s
    00h. 41m. 27s
    01h. 10m. 21s
    01h. 23m. 36s
    00h. 45m. 21s
    03h. 14m. 30s
    02h. 03m. 43s
    00h. 50m. 54s

    which I use CONCATENATE(LEFT(K10,2),":",MID(K10,5,3),":",MID(K10,10,3))
    and created
    time
    01: 48: 41
    00: 50: 04
    00: 41: 27
    01: 10: 21
    01: 23: 36
    00: 45: 21
    03: 14: 30
    02: 03: 43
    00: 50: 54

    NOW , My problem is that I want to use Conditional formating >> color scale
    and it doesn't color the area
    I have tried:
    1. formating the area as time but it doesn't work
    2. copying the data to notepad and back and it worked

    but I dont want to do it every time

    I understand that it consider this data as string

    Do You have an Idea, How to solve it ?

    Thanks
    Eli

    1. Hello, Eli,

      Please use the correct formula below:
      =CONCATENATE(LEFT(K21;2);":";MID(K21;6;2);":";MID(K21;11;2))

  44. How do i find identify unique value of a cell with concatenated values.
    E.g.
    Column A
    10, 10, 10
    10, 20, 30
    10, 30, 20
    10, 20, 30
    40, 40, 40

    How do i get results show the following unique concentrated values:
    10,,
    10, 20, 30
    10, 30, 20
    10, 20, 30
    40,,

    1. three steps:

      first you seperate the values by "," by using text to columns option.

      then values will be

      10 10 10
      10 20 30
      10 30 20
      10 20 30
      40 40 40

      then use true or false like =10=10 for first two and same thing to next
      remove all true values then concatenate by using ,

      I think this my help

    2. Hello, Sarah,

      Sorry, it's difficult to give you an exact formula without seeing your workbook. Please try the following one, it may help:
      =IF(AND(C6=D6;C6=E6);C6;CONCATENATE( C6;D6;E6))

      1. Dear Sarah,
        Use the formula,
        =IFERROR(MID(TRIM(A2),1,FIND(",",TRIM(A2),1)-1)&IF(MID(TRIM(A2),1,FIND(",",TRIM(A2),1)-1)=MID(TRIM(A2),FIND(",",TRIM(A2),1)+2,FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1)-FIND(",",TRIM(A2),1)-2),",",", "&MID(TRIM(A2),FIND(",",TRIM(A2),1)+2,FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1)-FIND(",",TRIM(A2),1)-2))&IF(OR(MID(TRIM(A2),FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1)+2,LEN(TRIM(A2))-FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1))=MID(TRIM(A2),1,FIND(",",TRIM(A2),1)-1),MID(TRIM(A2),FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1)+2,LEN(TRIM(A2))-FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1))=MID(TRIM(A2),FIND(",",TRIM(A2),1)+2,FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1)-FIND(",",TRIM(A2),1)-2)),",",", "&MID(TRIM(A2),FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1)+2,LEN(TRIM(A2))-FIND(",",TRIM(A2),FIND(",",TRIM(A2),1)+1))),"")

        It will accept up to 3 numbers of any number of digits in cell A2.
        Change the cell reference as required.
        If numbers are always confirmed to be of 2 digits, then the length of the formula can be significantly reduced.

        For a seried of 10, 10, 20,
        the result will be 10,,20

        Kindly confirm if you need any changes.

        Vijaykumar Shetye, Goa, India

        Vijaykumar Shetye, Goa, India

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)