VBA create string array

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
Hi Guys

I have 3 columns A2:C10 where i put an Id in ie 12345
now some cells could have blanks, an Id or multiple Ids separated by a comma

what i want to be able to do is if the cell has an id or multiple ids then create a string like this
<found>
for some reason its lost my string concat

it should be like this

(Found)
(ID)123456(/ID)
(/Found)


(Found)
(ID)555555(/ID)
(/Found)


<found><found><found><found><found>If the cell is blank then skip over it and dont process it
If all cells are blank the exit sub saying no data to import

I hope this makes sense

thank
you</found></found></found></found></found></found>
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
Multiple id's separated by a comma, or a comma then space?
Where do you want the stings to go?
And in what format? Is it

(Found)
(ID)123456(/ID)
(/Found)

(Found)
(ID)123457(/ID)
(/Found)

or

(Found)(ID)123456(/ID)(/Found)(Found)(ID)123457(/ID)(/Found)

If there's only 1 id in a cell, is it just

(Found)
(ID)123456(/ID)
(/Found)

or

(Found)(ID)123456(/ID)(/Found)

Are you happy with a VBA solution, or do you want Power Query or formula's?
 
Last edited:

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
Hi

I need it in this format and it will need to be VBA

(Found)
(ID)123456(/ID)
(/Found)

(Found)
(ID)123457(/ID)
(/Found)

it should be stored in sheet output cell A1

problem is that it should be seperated by a comma then space but sometimes agents could do it in just comma or comma then space or double space so will need to get values only between the commas
 

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,640
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top