Concatenation Question

Hi,

I have 100 row, single column list of text strings.  I am wanting to 
concatenate all these together, but with " Or Like " between each string. 
This is to enable me to prepare a long criteria expression for Access 
without all the re-typing.

And for the life of me I can't think of a simple, quick way.

Any help greatly appreciated.

Jason 


0
dummy8613 (43)
4/13/2007 12:38:36 PM
excel 39879 articles. 2 followers. Follow

3 Replies
273 Views

Similar Articles

[PageSpeed] 0

Hi

Check out JE McGimpsey's Multcat function
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you use " or Like " as your delimiter, it should work fine.
-- 
Regards

Roger Govier


"Jay" <dummy@dummy.dummy> wrote in message 
news:uL8QqicfHHA.5056@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I have 100 row, single column list of text strings.  I am wanting to 
> concatenate all these together, but with " Or Like " between each 
> string. This is to enable me to prepare a long criteria expression for 
> Access without all the re-typing.
>
> And for the life of me I can't think of a simple, quick way.
>
> Any help greatly appreciated.
>
> Jason
> 


0
roger5293 (1125)
4/13/2007 1:12:45 PM
Jay

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
    On Error GoTo endit
    w = InputBox("Enter the Type of De-limiter Desired")
    Set z = Application.InputBox("Select Destination Cell", _
            "Destination Cell", , , , , , 8)
   Application.SendKeys "+{F8}"
   Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
            "Cells Selection", , , , , , 8)
    For Each y In x
        If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
    Next
    z = Left(sbuf, Len(sbuf) - 1)
    Exit Sub
endit:
    MsgBox "Nothing Selected.  Please try again."
End Sub

In de-limiter inputbox enter <space>or like<space>




On Fri, 13 Apr 2007 13:38:36 +0100, "Jay" <dummy@dummy.dummy> wrote:

>Hi,
>
>I have 100 row, single column list of text strings.  I am wanting to 
>concatenate all these together, but with " Or Like " between each string. 
>This is to enable me to prepare a long criteria expression for Access 
>without all the re-typing.
>
>And for the life of me I can't think of a simple, quick way.
>
>Any help greatly appreciated.
>
>Jason 
>

0
Gord
4/13/2007 7:00:14 PM
Thanks Guys, most appreciated.  I've installed the UDF into my UDF 
module & will be trying your vb as well Cord.  Many thanks,

Jason


Gord Dibben wrote:
> Jay
> 
> Sub ConCat_Cells()
> Dim x As Range
> Dim y As Range
> Dim z As Range
> Dim w As String
> Dim sbuf As String
>     On Error GoTo endit
>     w = InputBox("Enter the Type of De-limiter Desired")
>     Set z = Application.InputBox("Select Destination Cell", _
>             "Destination Cell", , , , , , 8)
>    Application.SendKeys "+{F8}"
>    Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
>             "Cells Selection", , , , , , 8)
>     For Each y In x
>         If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
>     Next
>     z = Left(sbuf, Len(sbuf) - 1)
>     Exit Sub
> endit:
>     MsgBox "Nothing Selected.  Please try again."
> End Sub
> 
> In de-limiter inputbox enter <space>or like<space>
> 
> 
> 
> 
> On Fri, 13 Apr 2007 13:38:36 +0100, "Jay" <dummy@dummy.dummy> wrote:
> 
>> Hi,
>>
>> I have 100 row, single column list of text strings.  I am wanting to 
>> concatenate all these together, but with " Or Like " between each string. 
>> This is to enable me to prepare a long criteria expression for Access 
>> without all the re-typing.
>>
>> And for the life of me I can't think of a simple, quick way.
>>
>> Any help greatly appreciated.
>>
>> Jason 
>>
> 
0
spam6370 (39)
4/14/2007 12:37:53 PM
Reply:

Similar Artilces:

A difficult VBA question...a challenge
ok, I'm not a pro, but this task seems like an extremely difficult task - so hopefully someone is up to the challenge! I have a MS Access 2003 database. I would like to use VBA to accomplish the following tasks (keep in mind this is originating from Access and not Outlook)... I would like to take an email address and instead of creating a new message to it I would like to REPLY to the last one that was received from that address. Obviously the only real difference is that the body of the message from the previous received message will be included. Keep in mind I am talking about sendi...

Question about sort
I have a workbook with one sheet (Sheet1) In cell a1 I have the value "1" In cell b1 I have the formula "=a1" In cell c1 I have the formula "=Sheet1!a1" Select a1:c9 and do fill down. You obtain 3 identical columns with numbers from 1 to 9 from top to bottom. Try now to sort the area a1:c9 according to column a Descending (no headers). Questions: Is it normal that column c is in reverse order compared to a and b? Is there any explanation about this behaviour? Seems like formulas with references containing "!" are taken into consideration by sort bu...

Excel vba question
Is there any excel vba code that will accomplish the following: I have data entered in cells A1 thru E1 lets say (Name, Address, Zip, Country, and Phone). What I would like to be able to do is enter the requested information directly in the cells that contain the info as mentioned above. Then if I make a mistake or delete the info I typed in all together the original data returns. Example, In cell B2 it shows Address, so if I type in my address the word Address is replaced with my acutal address however, if I deleted my address the word Address would reappear. Hope someone out there underst...

CWebBrowser2 question
Hello, I am having trouble loading current .svg document when my OnInitDialog() is called. I have a line: m_explore.Navigate("c:\\Data\\SVG\\web\\standalone.html", NULL, NULL, NULL, NULL); standalone.html file uses 1 frame: main.html, main.html uses 2 frames: left.html and maps.svg, maps.svg uses xlink:href command to access mymap.svg This is what happens: 1) User opens a map and it gets converted into SVG 2) m_explore.Navigate() is called and displayer CORRECT map 3) User closes this map, opens another map and map from 2) is shown which INCORRECT. When the map is updated and ...

Question on "reply" format
Is there a way from within OE 6 to customize the "reply header"? I'm aware of the plugins, but I would like to do it with OE alone, if that's possible. I'm in the process of weaning myself off AOL ond so don't have a lot of experience with Outlook. Thanks, John Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomste...

Z report question
Is it possible to show the total qty of each of all the items sold from a particular department on the Zreport? ...

Conditional query question
I can tell I had a rough week-end because my brain isn’t working today. I have the below query with a one to many join. SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON public_ipm_bookings.oid = public_ipm_booking_items.oid; Below is a sample of the data returned. booking_id item_type C122742 Day Tour C122742 Flight C122742 Tour C122742 Day Tour C122865 Hotel C122865 Rail C122865 Hotel C122881 Day Tour C122881 Flight C122881 Rail C122881 Hotel C122894 Free Form C122945 Day Tour ...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Query question
Hi, I am stuck with a programming problem and I'm wondering if anyone can help me. Running SQL 2005. Here's a sample of my data: CREATE TABLE #temp (class varchar(1), referral varchar(2)) INSERT INTO #temp VALUES ('A', 'BB') INSERT INTO #temp VALUES ('A', 'AA') INSERT INTO #temp VALUES ('B', 'BB') INSERT INTO #temp VALUES ('B', 'CC') INSERT INTO #temp VALUES ('C', 'AA') INSERT INTO #temp VALUES ('C', 'BB') INSERT INTO #temp VALUES ('A', 'DD...

Combo question
Is there any way to make a combo look like a text box, that is just have one answer displayed that is there and you don't have to scroll to see the result? -- Milton Purdy ACCESS State of Arkansas "golfinray" <golfinray@discussions.microsoft.com> wrote in message news:C0230230-B690-4E03-9073-337576F7DB71@microsoft.com... > Is there any way to make a combo look like a text box, that is just have > one > answer displayed that is there and you don't have to scroll to see the > result? I'm not following you. A combo normally displays on...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Ultimate Basic question: What exactly IS publisher?
Okay, okay, I should know. But my wife is considering getting Publisher, and has previously worked with Word for text things, and sometimes with--gasp--Print Shop to produce basic graphics-based things like cards and posters. But what does Publisher do that Word doesn't? And does it do anything like a Print Shop type program, or is it something completely different? Word or Publisher? Use Crabby's special recipe to decide http://office.microsoft.com/en-us/help/HA101757591033.aspx Publisher 2007 Help and How-to http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx --...

Math Question
This is probably real simple for somebody, I just can't get it. If I want 50% more of something in cell A1 I would Type: =SUM(A1)+(A1*50%) So if A1 is 14 the result would be 21...Perfect. But if A1 is (14) the result is (21) Not the (7) I wanted. Can anybody help??? First, you SUM() function is superfluous. SUM(A1) returns A1. I'm not sure why you think that positive 7 should be 50% of negative 14, but you can achieve your goal using =A1 + ABS(A1)/2 In article <9F6DFDFB-AEAA-4723-81FA-265E9E077F12@microsoft.com>, "Mike" <Mike@discussions.microsoft.com...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

Format Question
I have the following code that makes each line red when the number of workingdays >10: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If WorkingDays2([Date_Filed], [Summary_Date]) > 10 Then Me.Section(acDetail).BackColor = 255 Else Me.Section(acDetail).BackColor = 16777215 End If Everything works fine however upon looking at the report i would rather have the row of fields in my report change font color to red instead of the section. One of the fields in the report is me.case_no. I have tried to add the code of : If WorkingDays2([Date_Filed], [Summary_Date]) ...

Hyperlink Question #9
Hyperlinks such as: =HYPERLINK( =HYPERLINK($A$1&B2,"3") works, but don't know how to fix: =HYPERLINK("webaddress.com="&$A$1,"3") does not. It goes to the web page, but tries to use: $A$1 as final symbol (not what is in cell A1, which is desired) How do I make the 2nd example work? Thanks Hello Nastech, I suspect a typographical error is causing your problem. Recheck the placement of the double quotes. SIncerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Pro...

Newbie questions #2
I have installed Exchange 2003 on Windows Server 2003. I have read the Administration Guide, but I still have a couple of questions: 1) How do I record / view users' email? 2) How do I access the postmaster account? 3) How do I send all undeliverable mail to an admin account? TIA Nick 1) enable journaling http://support.microsoft.com/default.aspx?scid=kb;en-us;261173 2) in outlook (2003) go to tools -> email accounts -> view or change existing accounts highlight exchange server and click the 'change' button, then the 'more settings' button click the adv...

Installation question
I've been trying for two days to post a message about having multiple versions of Publisher installed but they don't appear here. I know they go through somewhere as they show in the m.p.pub ng on another server. My messages up to 2/5 posted just fine. I do not have any rules that could cause this & I haven't kill-filed myself. What could be the problem? Using WLM under XP/SP3 bj IN WLM Tools/Options/ - uncheck use newsgroup community features -- ...winston ms-mvp mail "bj" <bjones44@bellatlantic.net> wrote in message news:#KplkjfqKH...

A few DDX questions
Hi, I am thinking of using DDX. I understand that it is all-or-nothing affair in this sense you call UpdateData(BOOL bUpdateDirection) when all the controls need refreshing from various member variables or the various member variables are updated from the controls. (i) What happens if one or more of the controls are disabled? Does DDX still work for the disabled control? (ii) I have a simple dropdown list combo box. Depending on the current choice, various controls on the dialog box are enabled/disabled. The same is true for various other controls. Under these circumstances, is using DDX o...

Exchange 2007 setup question
Hello all My company just spent a grip of money on a SAN from HP and two HP 64 AMD servers that are for Exchange 2007. We support around 300 users. One of the admins wants to setup the two HP 64 bit servers as a cluster, and normally i would think this would be a good decsion, but because Exchange 2007 has 5 seperate roles now, is it wise to setup 4 of the roles on the cluster? Example would be the mailbox hub transport, UM, and CA would be isntalled on the cluster. Each of the HP servers are Dual opteron 280s with 9 gigs of RAM, so this should be enough juice to support 300 users. ...

Question About Extension DLL
Project X uses an extension dll. If I find a bug in the extension dll, fix and re-build it, do I have to re-link the main project with the new .lib in order for it to work correctly or can I simply distribute the new .dll and it will work fine with with old .exe? Luke If all your did was change the code, and the interface is the same, you can just redistribute the code. If you changed the .h file, you have potential compatibility problems. But if you only changed a .cpp file, or an internal .h file never seen by your clients, you should be safe. joe \On Wed, 26 May 2004 06:46:20 -0400,...

two more questions
Can I lock some columns on a sheet so they are not editable ? And I am new to excel and needed to calc several fields from different worksheets onto one "totals" worksheet. How do I do this? Thanks over and over again : ) Mary, by default all cells in excel are locked, if you only need a few locked I would select them all first, Ctrl A, then go to format, cells, protection and uncheck locked, then select the cells or columns you want to lock and go to format cells and check locked, the go to tools, protection, and protect sheet, enter a password if you want, now the cells that ar...

IF and Concatenate
Hi I have a following chart which list out delivery dates arcross the top with items and units on the body of the chart (dashes are spaces) On the right column is the results that I need. What kind of formula can I use to return such results? I thought that I can use IF and Concatenate formula (IF, ordered units, then seek out date....). Not even sure I can use IF, since in my real chart, I have more than 20+ dates going across. Any help would be much appreciated! Style---7/30---8/13----8/14----8/15---8/17--------Wanted-Result 66106-------------------------------9--------5--------9 DUE ...

Javascript question
I received an e-mail with an attachment that requires I 'enable javascript in my browser settings'. I don't know in which browser settings I would find that. "Joy" <joymp2@nospambellsouth.net> wrote: >I received an e-mail with an attachment that requires I 'enable javascript >in my browser settings'. I don't know in which browser settings I would >find that. Whichever is your default browser, I suppose. On the other hand, since it's an email, it may be talking about your email client. Browsers all (I think) have Ja...

Question about splitting a #
If there a way to do this: I want to make a sheet that when I input a number it breaks out into to cells, for example: I input 1200 in to A1 I want B1 to show 1000 of that and B2 to show the remaining 200. Thanks for the help Felix -- nyrfan ------------------------------------------------------------------------ nyrfan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31192 View this thread: http://www.excelforum.com/showthread.php?threadid=508602 See replies in .Misc Biff "nyrfan" <nyrfan.22qi7a_1139106000.61@excelforum-nospam.com> wrot...