Results 1 to 9 of 9

Text in Concatenated cell, Need add comma after every 4th character

Threaded View

  1. #1
    Registered User
    Join Date
    03-08-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    27

    Text in Concatenated cell, Need add comma after every 4th character

    Hi Guys,

    Hoping someone can help me here.

    I have a spreadsheet with approx. 3000 rows of data.

    There is a number (formatted as text) in column A of each row.

    Columns B through to G contain a 4 character reference (can consist of letters or both letters and numbers).

    Reading across each row, it is unlikely that their is an entry in each column (ie some could be blank).

    I would like a formula that will read across reach row (columns B to G) and put them in a cell on the same row (ie column I) as comma separated values.

    If a particular column is blank to don't want to two (or more) adjacent commas.

    The way I have thought of (and I know there is must be a simpler way), is to use the concatenate function in column H (for row values of the columns G to H). Then using the MID function on the Concatenated value in H whilst inserting a comma after every fourth character and placing the result in column I.

    Although, shown in my example (attached spreadsheet) I don't want a comma after the last set of four characters (ie I don't want a comma on the very end of the string in column I).

    Any help or guidance anyone can offer would be great.

    Kind regards

    Michael
    Attached Files Attached Files
    Last edited by mike.greene; 09-01-2015 at 03:02 AM. Reason: spelling error

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create a text box for each row of data and fill that textbox with concatenated cell value.
    By Goldbadger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2015, 07:19 AM
  2. Replies: 3
    Last Post: 11-25-2014, 05:17 PM
  3. Find and remove all text to the left of last comma (including comma) in a cell
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-05-2013, 08:47 AM
  4. How to display text in a cell with a mailto concatenated hyperlink?
    By JRR007 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2012, 11:18 AM
  5. [SOLVED] If comma present, then place a character before and after comma (FORMULA)
    By jaimealvarez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2012, 04:04 PM
  6. Macro to find character and delete all text in cell after the character
    By SpencerRichman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2012, 06:08 PM
  7. Copying text from concatenated cell
    By gmanob in forum Excel General
    Replies: 2
    Last Post: 07-10-2007, 03:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1