Concatenating and splitting strings

Posted on by Frank Kabel

As processing strings with formulas is kind of fun just to add some more formulas for this.
1. Concatenating strings
As you probably already know Excel’s worksheet functions do not support string concatenation for an array. e.g. the following:
=CONCATENATE(A1:A10)
does not work. Typical suggestion in these cases would be to use a UDF (or an add-in such as MOREFUNC.XLL with the function MCONCAT).
For only small ranges you could still do it with worksheet functions. The problem is how to deal with blank cells if you want to use a delimiter between each filled cell and without checking each single cell for blank.

So let’s have a look at the following example:
fk_string_1

Formula used (in cell F1): =SUBSTITUTE(TRIM(A1&” “&B1&” “&C1&” “&D1),” “,”-“)

As you can see this formula concatenates (using the hyphen as delimter) the strings in column A-E and skips blank cells. This formula has one restriction though: The concatenated cells are not allowed to contain spaces. So if you need a more complex solution best to use VBA for this.

2. Splitting strings in characters
The following is probably more a fun application but if you want to split a string value into its single characters you can achieve this with the following formula:
fk_string_2

Formula used (in C1): = MID($A1,COLUMN()-COLUMN($C1),1)
and copy this formula across.

Frank

Posted in Uncategorized

19 thoughts on “Concatenating and splitting strings”

  1. Hi.

    I just fell on this:Splitting strings in characters.

    It is exactly what I needed to give me a head start with something I have to do for an author friend(not remunarated work).

    Only, I tried it and it was not working. Any details, insight?

    Thanks.

    Y

  3. Hi Yzabel, The formula used (in C1) should read: =MID($A1,COLUMN()-COLUMN($C1)+1,1)
    as MID needs the second parameter to be greater than zero.

    HTH

  4. Hi;

    Great tip – I have a need for a slight variation on this however: A spreadsheet that I’ve made for a colleague requires numerical values (6 digits) separated into individual cells, however, to make mathematical sense, this needs to be done from right to left (i.e so the ones are in the ones column, tens are in tens etc). Is there a switch can be added to work Right to Left?

    Cheers,

    Phil

  6. Best thing to do would be to create a 6-digit string and then select your digit.

    =mid(CONCATENATE(REPT(“0?,6-LEN(A1)),A1),C1,1)

    A1 contains the number. C1 contains the digit number that you want…

  7. @Dan

    There’s an easier approach. With value in A1, enter this formula in B1 then fill B1 right.

    =MID(TEXT($A1,”000000?),COLUMNS($B1:B1),1)

  8. I replied to this last week, but this site must not have liked my escaped HTML characters. I’ll try again.

    It’s possible to handle embedded spaces in the text values if you’re willing to put up with long formulas.

    =MID(IF(A1=””,””,”-“&A1)&IF(B1=””,””,”-“&B1)
    &IF(C1=””,””,”-“&C1)&IF(D1=””,””,”-“&D1),2,32768)

  9. Thanks all;

    I’ve got with a variation on the LEN function suggestion: works treat. I’d never heard of that function before, so thanks for that!

    Phil

  10. I thought I’d just quickly post the original solution that I’d devised for this problem – some of you may find it amusing in its clumsiness.

    Say our 6 digit number (A1) was 654,321. Of the ‘single digit’ cells, the first one (C1) was simply Rounddown(A1/100000) to one digit. The second cell from the left was Roundown (A1-(C1*100000)/10000) and so on, stripping off the digits one by one by rounding down and dividing. Very very messy by the time I got to the 6th cell.

    Phil

  11. Thanks for the concatenating strings tip. It worked great to concatenate multiple categories from an row array that included blank cells. I used a comma separator, not the dash, to make them suitable for upload into Outlook categories as part of an overall contact upload.

  12. Could you help me to convert: 123456789,1234.jpg
    to: “123456789?,”1234.jpg”

    Can I use concatenate formula?

    Thanks.

  13. Hi,

    Thanks for the tip. But I have some question. I want to split a string like this “Hello World” into “HW” or “Go go go” into “GGG” in a cell, “Nothing gonna change my love for you” into “NGCY”, what function should I use?

  14. Hi…. I have a similar problem.
    I want to split the date into date, month and year.
    can anyone tell me how to do that?

    Thanks in advance!!

  15. Saket –

    Assuming you have the date as a true date (a number, not text looking like a date) then use the DAY(date), MONTH(date) and YEAR(Date) functions.

    If the date is text, but in a date format Excel understands, then use the DATEVALUE(text) function first, inside the DAY(), MONTH(), and YEAR() functions.

    If it’s not a format Excel understands, provide an example and we’ll go from there.

    …mrt

  16. Can you help me guys how to split this kind of string in two columns. e.g ” AnneRose, “TomCruiseJr” .. The trigger is 2nd Capital Letter.

  17. Manny:

    With AnneRose in A1

    B1: =LEFT(A1,MIN(IFERROR(FIND(CHAR(ROW($65:$90)),A1,2),LEN(A1)+1))-1)

    C1: =MID(A1,MIN(IFERROR(FIND(CHAR(ROW($65:$90)),A1,2),LEN(A1)+1)),LEN(A1))

    Both are array formulas, so enter with Control+Shift+Enter.

  18. Text parsing is simpler with the proper tools. If this is for one person’s use, it may be better in the long-run to download and install Laurent Longre’s MOREFUNC.XLL add-in and use it’s REGEX.MID function.

    B1: =REGEX.MID($A1,”[A-Z][a-z]+”,1)
    C1: =SUBSTITUTE(A1,B1,””,1)

    Even if only built-in functions may be used, the C1 formula above is all that’s needed.

  19. hi,

    i have a string (abc_def_ghi_jk) and i want to capture ghi from the string using formulas in excel. Can some one pl help

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.