Adding |Pipe| to values in cells

Hi I have a column of barcodes. I need to get a formula or script to put |Pipes| as the before the 1st and after the last character in the cell.

E.g. barcode 123456789 should be converted to |123456789|

I then want all the individual barcodes to appear in 1 cell next to each other

E.g. in one cell |123456789|987654321|244346473|686541234|479854635|132498569| and so on.

Thanks in advance!

Hi talan, welcome to the forum.

This is called concatenation and is simple with just a few cells. STRING CONCATENATION is not really a built-in function of Excel (stringing together an entire range) so has to be added as a UserDefinedFunction (UDF).

Your needs are pretty simple. Here is a very basic UDF that will string together any range you list:

Please Login or Register  to view this content.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
========

In a nutshell, the function is used like so:

=CONCATRANGE(A1:A100,"|")

The first parameter is the range
The second parameter is the delimiting character you want.

Since you want the PIPE to appear before and after your formula, insert those manually, like so:

="|" & CONCATRANGE(A1:A100, "|") & "|"

Actually, I realized we could just add the "capping" of delimiting characters to each end as an optional parameter in the UDF, since we're using a UDF anyway.

Here's the updated code:

Please Login or Register  to view this content.

And the updated formula use would be:

=CONCATRANGE(A1:A100, "|", 1)

The added third parameter is optional. If you leave it off or use a zero, then the UDF will not put capping delimiters at the start and end. If you put in a 1, you will see them.


NOTE: Regardless of which UDF you use, if you concat a LONG column of values together, you may not be able to see the whole thing in Excel's display. That doesn't mean they aren't there.

You can copy the cell with the formula, then EDIT > PASTE SPECIAL > VALUES to turn it into a flat string.

Another variant based on column based data which avoids need for iteration would be:

Please Login or Register  to view this content.

the above utilise's JB's optional prefix/suffix flag and is called in the same way

Please Login or Register  to view this content.

the premise here is to post the values to a variant array and then re-join the values based on the delimiter of choice.... the above is definitely less robust than JB's however in terms of handling blanks should they be interspersed amongst valid values.

Originally Posted by DonkeyOte

Please Login or Register  to view this content.

...the above is definitely less robust than JB's however in terms of handling blanks should they be interspersed amongst valid values.

Actually, that's not true. Yours "almost" handled that, too. One small tweak and I think we've got a much better basic string concatenation tool that uses no loop at all. Awesome idea, Don.

Here's the adjusted version:

Please Login or Register  to view this content.

Still utilized the same way:

Please Login or Register  to view this content.

Hmm, I think I just discovered what you meant by "column based" data. This array approach would need some more massaging to be universally usable for

1) columnar data
2) row data
3) 2 dimensional data

JB, the issue with that approach remains the same in that blanks in the referenced range will cause issues... if one were to assume no blanks (interspersed or not) then there's no need for the Replace at all.

I think this approach would work correctly regardless of position of blanks in range:

Please Login or Register  to view this content.

I used WF Trim rather than VBA Trim, the latter would not return correct result

By hardcoding the " " into the replace, it turns one cell two=word strings ("Apple cart") into two cell 1=word strings (Apple|cart).

My version above solves that, but it doesn't do row ranges. How would we go with A1:I1 instead of A1:A9?

Correct. Let's assume

A1: Apple Cart
A2: Bananas
A3: [blank]
A4: Carrots

and

A5:A100: [blank]

Neither version will work, output of my prior code would be:

|apple|cart|bananas|carrots|

And yours:

|apple cart|bananas|carrots|||||||||||||||||||||||||||||||||||||||||||||||||

One workaround would be to use Evaluate to replace the bona fide spaces prior to processing the strings and then revert to space thereafter, eg:

Please Login or Register  to view this content.

which would generate:

|apple cart|bananas|carrots|

though of course if there are valid ^ in the string...

Originally Posted by DonkeyOte

...And yours:

Please Login or Register  to view this content.

?? My final solution to the OP was:

Please Login or Register  to view this content.

And from your sample data suggested above, that UDF gives the results:

Please Login or Register  to view this content.

I'll have to analyze your suggestion, but I still think your original version works with the amendment I suggested. The only thing I'm trying to learn is how to fill the vData with values when the RngVals is in row format.

Sorry by your suggestion I was referring to your initial revision to my approach!

The latest version should work for blanks (wherever they appear) and cater for strings that contain spaces without need for iteration.

I will put together a version for vector/matrix.

My first tweak, then a "possible" loop added that will only loop very briefly if at all. For StrConcatenation, one would think the blanks would be minimal, so this is most likely the smallest additional calcs, if any, and it stays with the original approach to do the other 99% of the work.

Please Login or Register  to view this content.

I have little doubt this could be streamlined but I need to get on with some other bits & pieces so in the meantime...

Please Login or Register  to view this content.

this should work for both vectors & matrices regardless of blanks etc... for everything except strings that contain valid ^

regards ^: it would pretty straightforward to create a constant array of "unusual chars" and iterate the array until such time as you find a char not present in the range of values being processed and subsequently code that into the replace function.

The original suggestion in post #2 does the job. Install that into a sample workbook and try it with your sample data. IF you can't make it work, post your workbook so I can see your work/attempt.

Glad you got it worked out.

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.