Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Un-Concatenate

I have a document that has a whole bunch of text in one cell, each 'section' of information is on a seperate line and has a square character in the beginning.

Example:

 Understanding Insider Trading (706)
 Customer Data Protection (703)
 Communicating Electronically (702)
 A Guide to Outside Business Activities (707)
 Selling Away (708)
 AML Update (available March 2008)
 Information Barriers: Preventing Disclosure (available March 2008)
 Ethical Decisions are Not Options (available May 2008)
10

How could I split this up into different cells for each 'course' in this example. The cell does not have to start with . Those are just used as bullets with the way it is now.


Post your answer or comment

comments powered by Disqus
I have cells that are populated with strings like 'A2-1' - I would like to separate those figures into new cells - one cell with 'A', one with '2' and one with '1' - break up that data - sort of 'un-concatenate' - is that possible? How can I do it? Thanks again for the help - you guys are lifesavers.

Hi,
I have full address in once cell and I was just wondering if there any way to un-concatenate it in all different cells. Here is the example

Cell A1

16545 Dingo St
suite 656
Chicago, IL 54545

Please help me if you know how to save all of that cell into 5 different cells (address1, address2, city, state, address).

Thank you so much in advance

I am pulling list from a database that has a column that contains the name + title, basically has been concatenated in the master database that I do not have access to. I need to separate this info back out. There is no one delimiter that works due to the inconsistent caps, name and title structures. The main thing that distinguishes the name from title is upper/lower case and Spaces.

Sample
Verne Gingerich VICE PRESIDENTNew THomes DATA PROCESSINGLeonard GrillMANAGERMARTIN KEHOE PRESIDENTAnthony Glavin OTHER COMPANY PERSONNELJHK LLC OTHER COMPANY PERSONNELGerald R Cox VICE PRESIDENTKenneth Long VICE PRESIDENTBryan KingVICE PRESIDENTDavid Derry GEOGRAPHICMANAGEMENTJames P Sieverkropp SECRETARY
The combining of these fields is the way the original vendor of this data waters-down specific versions.

Thanks for reading my post.

Hi all,

I want to invert a selection in a column before pasting it.
For eg: I have a whole lot of info from Row A1:A45
When I paste it into column B, I want it to flip; i.e.: the selection to
invert—That is, the contents of cell A45 to go into B1, A44 to go into B2 and
so on till I get A1 in B45.
I need to do this a number of times so would rather not do a concatenate
with numbered rows, sort ascendng/sort descending and un-concatenate agian...
is there something you have seen that could help?

I don’t know if this can be done but over the past week I wonder why it has
not been provided for and I wonder why there is help topic so I can figure
this out on my own.

thanks!

Greetings all,

I'm new and inexperienced.

My problem - to unconcatenate - to split a string into its leftmost
character and the remainder (which may be nothing).

I guess I can strip the leftmost alpha/numeric character from the string
using the LEFT function.

What is the best expression for my remainder?

Thanks in advance,

Rednelle

I have a column that has a list of firstnames and surnames in it.

Any idea how I split this out into a single column of first name and a
single column of surnames?

Thanks!

Hello there,

I have a large list of police records and one of the fields contains their,
ID Code, Area and Rank seperated by spaces. I know there is a LEN and FIND
fuction but how do I conbine these??

Thankyou in advance

Judith

Hiya,

Basically i want to be able to extract the month only when the full date has been input into the sheet?

Is there an unconcatenate type function?

thanks

EM

Hi All,

I was wondering if there was a formula in which you can split text in a cell. something like an =FIND formula or even an "UN-CONCATENATE" as such.....

For Example

In Cell A1 I would have - BLOGS JOE
In Cell A2 I would have - CARTER WILLIAM
In Cell A3 I would have - SMITH JOHN

What I would like in cell B1 would be something like - "=FIND(" ",A1,and count back to the beginning of the cell and copy and paste details here)

What I would like in cell C1 would be something like - "=FIND(" ",A1,and count back to the end of the cell and copy and paste details here)

in order to give

A1 - BLOGS JOE
B1 - BLOGS
C1 - JOE

and then all I'd have to do is copy the formula down....

Thanks,
Jon

I have a string of numbers 00633.84010.12740.0000.10101 that I need to break
out into the separate components.

Morning all..

i am after some help.. i have a list of IP address which i need to split down

eg.

192.168.0.1
192.168.0.2
202.132.233.7

i need to split this down in excel to show in 4 columns without the dot. i know u can do it manually with "Text to Column" but these numbers are always changing and it time consuming to do it that way.

please some one tell me how to split my IP Address down in to 4 columns

thanks and kind regards

Lee Keene

Good Afternoon!
I am having an issue with trying to figure out how to program a specific macro in VBA using Excel 2007. This macro that I want to develope will concatenate a range (single rows only (example: select C20:X20)) of X (variable) amount of Cells into 1 single cell, preferably the cell preceeding the selected range (ie B20). The issue is that not all the cells in that range would be filled and each row has a different column filled in with text. Thusly, how do I program a macro that will allow me to select a range of cells, pick out only the cells filled with text, and concatenate those cells into a single cell just preceeding the range in the same row? Then from there i would be able to copy that macro and and paste it in an infinite number of rows below each row selecting it's own row range next to it and applying the concatenation (example concatenate C20:X20, copy and paste the macro to the next cell and it will automatically select and concatenate C21:X21... etc...). Thanks for taking the time in considering my post

thanks!

Hello,

This one seems tricky to me.
I basically want any duplicate email addresses to auto concatenate the tags.
Example the first 3, a@mail.com, would give the final cell result of "Retail, Commercial, Investment".
A few of them have just 2 and a few have 5 duplicate addresses.

Any help would be appreciated.

Thank you

Edit: I should also add that there are a maximum of 5 tags: Retail, Commercial, Office, Investment, Industrial
Anything less than that will have a different combination of tags, so there can't be a static formula.
Sample April 02.xlsx

I am concatenating the text in a number of cells using MCONCAT but the character limitations in the cell are displaying a #VALUE! error.
Is there a way around this? I need to allow for about 850 characters.
Using Excel 2003.

Hi Everyone,

I have posted this in another forum, which you can see here: http://www.excelforum.com/excel-prog...mbination.html.

I am working on a project where I have 12 adjective keywords (N2:Y2) and 4 noun keywords (J2:M2). I need to concatenate every unique(no repeating words) combination of these two groups so that it looks like:

N O P J
N O J
N P J
O P J
N O P M
N O M
etc...

Sequence is not important for the adjective keywords, so I don't want to have N O J and O N J. The only part that is important is that the noun keywords (J2:M2) stay at the end of the concatenation.

If there is a blank in any of the keyword cells for a combination, I would like the corresponding concatenated cell to be blank as well. I would like to put the first concatenated cell in Z2, keeping all concatenations in the same row.

I've attached a test file below. Please let me know if any clarification is needed. Any help would be greatly appreciated! Thanks!

I'm guessing this will be a snap for most of you, I've been stumbling through VBA for a few weeks now and have found many solutions on Ozgrid...so thanks to all those who've contributed your expertise!

I am creating a tabbed userform. The first tab is used to enter a person's demographics into a registry with first name, middle initial and last name as distinct fields input into a dynamic range. The subsequent tabs have comboboxes whose RowSource is the dynamic range. I have displayed all three columns in the drop down list just fine however when a person is selected only the first name displays in the textbox. I would like the person's full name to display in the box once selected. I am trying to do this as elegantly as possible and seems there should be a better solution that creating a function to concatenate the fields and populate a single row on a hidden sheet to use as the RowSource.

Many Thanks!

Hey and thanks for having me. I'm not terribly fluent in VB or Excel and came across this forum searching for a way to CONCATENATE including ONLY cells which contain data, and it looks great.

I am building an HTML table (as well as a list) into an Excel row using the CONCATENATE function, with one row each for the opening and closing table (, etc) in two columns. The code below works, but I imagine there is at least one cleaner way to do it.

=CONCATENATE(M2&IF(N2="","",N2&"")&IF(O2="","",O2&"")&IF(P2="","",P2&"")&IF(Q2="","",Q2&"") & R2)

Another shortcoming of this formula is that it creates a one-row empty table even if there is no data. Example file attached.HTMLTable_ex.xlsHTMLTable_ex.xls

Thanks a lot for any input and have a great one.

Mike

First, I am wondering how to create spaces between the texts: when I do =CONCATENATE(A1,A2) it displays the two cells' text with no spaces between them - and I don't want to add the space in the origin cell.

I am also try to keep a phone # format, something like: =CONCATENATE("Please call John at","C1"), where C1 is the phone number. I have C1 cell formatted as phone number display, so you only have to type the numbers, but the formula spits it out as ##########.

Thanks for any help!

reduce variables on a graph.xlsx

I have a chart that displays all variables, as normal, but if some of the variables are not being used
it makes the chart seem un-tidy, harder to read and includes space not required, thus reducing the viewable span of the chart or graph. I was hoping there may be a couple of tricks and a function or two to solve this problem.. as in attachments.
ash

Hello All,

I have one column with only string values where I want to concatenate all into one cell. However, the I want to only include the values in column one if the corresponding value in column two is "Y."

Here is my non-functional code:


	VB:
	
 
    Dim NamesArray As Variant 
    Dim DatesArray As Variant 
    Dim i As Integer 
    NamesArray = Range(Names).Value 
    DatesArray = Range(Dates).Value 
    For i = 1 To UBound(NamesArray) 
        If DatesArray(i, 1) = "Y" Then 
            Attendees = Attendees & NamesArray(i, 1).Text 
        End If 
    Next 
    Attendees = Mid(Attendees, Len(Attendees) + 1) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am a big beginner here. Help is really appreciated!

Thanks,

John

I keep running into this error in the code, I ran this same code yesterday and it worked fine.

	VB:
	
 Concatenate() 
    Dim w As Long 
    Dim t As String 
    Dim Rng As Range, c As Range 
    w = 1 
    Set Rng = Selection 
    For Each c In Rng 
        If c  "" Then 
            t = t & c & " " 
        Else 
            w = w + 1 
            t = Left(t, Len(t) - 1)

Hello,

I am very new to using VB in excel.. and am looking for a way to combine a few of the columns in a generalized spreadsheet, and paste the results into the second sheet.

The deal is, I need to concatenate the contents of column B with a "-" followed by the contents of column C. Similarly with columns F and G (with a "-" in between). It also needs to allow for the number of data elements in the column, as it varies from spreadsheet to spreadsheet, and the solution needs to be generalized.

The reason for not just using the concatenate function (ie. "=concatenate(B4,"-",C4)") is for a couple of reasons:

1) The spreadsheet is in a general form we use for HUNDREDS of spreadsheets.. and I'm looking for a VB code that can be used in all of these for a copy and go solution.
2) The number of rows varies from spreadsheet to spreadsheet, therefore (as above) it needs to allow for this - by looking for the empty cell at the end of the column or something similar.

Any help is very appreciated.

Thanks in advance,
Nick.

Hi all,

I'm running into a frustrating problem with a macro I've written to pull financial statements for stocks based on tables on smartmoney.com. My goal is this:

1. Enter into a cell the ticker of the stock you want to query. Named range: ticker
2. concatenate ticker with appropriate URL
3. Grab table, insert into workbook.

Here is the base code from the macro recorder, which is very simple:


	VB:
	
 Frustration() 
     '
     ' Frustration Macro
     '
     
     
     '
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://www.smartmoney.com/quote/msft", Destination:=Range("$A$47")) 
        .Name = "msft" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlSpecifiedTables 
        .WebFormatting = xlWebFormattingNone 
        .WebTables = "1" 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .WebDisableRedirections = False 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And that works just fine. The problem is that I need to manipulate just a portion of the URL, not the whole thing. I need "msft" portion of the url (http://www.smartmoney.com/quote/msft) to equal the input from the named range "ticker". SO, I attempted to modify the code as follows:


	VB:
	
 Frustration() 
     '
     ' Frustration Macro
     '
     
     
     '
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://www.smartmoney.com/quote/" & Ticker, Destination:=Range("$A$47")) 
        .Name = "msft" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlSpecifiedTables 
        .WebFormatting = xlWebFormattingNone 
        .WebTables = "1" 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .WebDisableRedirections = False 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...and the query fails to get the appropriate table.

Any suggestions?

Thanks
C

Hey all,

My formula looks like this:

=INDIRECT(CONCATENATE("INDEX('Expanded Stats v40.xlsx'!Table2[",$A$2,"],MATCH(LARGE('Expanded Stats v40.xlsx'!Table2[",$A3,"],1),'Expanded Stats v40.xlsx'!Table2[",$A3,"],0))"))

A2 = one column name of Table 2
A3 = another column name of Table 2

The purpose is to find the largest value in Table2[A3] and then take that row number to find the associated name in Table2[A2]. (ie: Finds the maximum number of points in column A3 of Table 2, and then grabs the name of the person associated with that maximum from column A2 of Table 2 from the index function)

My problem is that even though I change the k-th value for the Large function, the value that is associated with the k=1 value is displayed every time. Again, the same value appears for the function regardless of what k value I enter.

Any help would be greatly appreciated - I imagine it is an excel rule that I am failing to abide by rather than a workbook issue (the workbook in question is open and my functions work by manually putting in the table column names without refering to A2/A3)


No luck finding an answer? You could always try Google.