Adding strings to current text

[gogent]

Hi, I'm trying to figure out what's the best way to add the following strings.

1. I've a list of incidents on a spreadsheet.

INC000010
INC000011
INC000012
INC000013
INC000014
INC000015

2. I want to add Incident ID*+' = " before each incident follow by a " Or.
I want it to look like this on a single cell.
'Incident ID*+' = "INC000010" Or 'Incident ID*+' = "INC000011" Or 'Incident ID*+' = "INC000012" Or 'Incident ID*+' = "INC000013" Or 'Incident ID*+' = "INC000014" Or 'Incident ID*+' = "INC000015"

Would the concatenate function be the best way to achieve this?

 

[Michael M]

do you want a lengthy formula or a VBA approach?
Where do you want the resultant answer ?
Where are the current list of incidents listed ?
As you can see, more information gets better results !

 

[Alex Blakenburg]

In addition to @Michael M question, can you update your profile to indicate what version of Excel you are on.

I am not clear on what output you want but if you have Excel 365 something like this might work for you.

="'Incident ID*+' = " &TEXTJOIN(" Or 'Incident ID*+' = ",TRUE,$A$5:$A$10)

 

[gogent]

Hi Michael,

do you want a lengthy formula or a VBA approach? Formula would be ideal since my work have some VBA restrictions
Where do you want the resultant answer ? Not sure what you mean by this.
Where are the current list of incidents listed ? Is listed on a spreadsheet.
As you can see, more information gets better results !


Thanks, Alex that's great! Your formula almost got it to where I want it to be. However, the quotes are not around the incidents. I tried to add some quotes to your formula but keep getting error messages.
Mine
'Incident ID*+' = "INC000010" Or 'Incident ID*+' = "INC000011" Or 'Incident ID*+' = "INC000012" Or 'Incident ID*+' = "INC000013" Or 'Incident ID*+' = "INC000014" Or 'Incident ID*+' = "INC000015"

Your
'Incident ID*+' = INC000010 Or 'Incident ID*+' = INC000011 Or 'Incident ID*+' = INC000012 Or 'Incident ID*+' = INC000013 Or 'Incident ID*+' = INC000014 Or 'Incident ID*+' = INC000015

 

[Michael M]

If you used VBA
Where do you want the resultant answer ? Not sure what you mean by this.....you would ned to tell us which cell / sheet to place the answer ?
Where are the current list of incidents listed ? Is listed on a spreadsheet.....sheet name/cell location ?

 

[Alex Blakenburg]

I couldn't find an elegant way of doing it but this should get you there:-

=SUBSTITUTE(
        SUBSTITUTE("'Incident ID*+' = "&TEXTJOIN(" Or 'Incident ID*+' = ",TRUE,$A$5:$A$10),
        " INC"," ""INC"),
" Or",""" Or")
& """"

 

[gogent]

=SUBSTITUTE(SUBSTITUTE("'Incident ID*+' = "&TEXTJOIN(" Or 'Incident ID*+' = ",TRUE,$A$1:$A$6)," INC"," ""INC")," Or",""" Or")& """"

Thank you so much. I think this is it and will try it when I return back to work next Monday. Thank you to you both for your time and have a great day!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[Fluff]

Another option

+Fluff 1.xlsm
	J	K
1
2	INC000010	'Incident ID*+' = "INC000010" Or 'Incident ID*+' = "INC000011" Or 'Incident ID*+' = "INC000012" Or 'Incident ID*+' = "INC000013" Or 'Incident ID*+' = "INC000014" Or 'Incident ID*+' = "INC000015"
3	INC000011
4	INC000012
5	INC000013
6	INC000014
7	INC000015
8
Main

Cell Formulas
Range	Formula
K2	K2	="'Incident ID*+' = """&TEXTJOIN(""" Or 'Incident ID*+' = """,TRUE,J2:J7)&""""

 

[gogent]

Great learning experience from you both! Thank you