Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel convert Numbers vertical to horizontal with ;

Posted on 2014-08-08
6
Medium Priority
?
150 Views
Last Modified: 2014-08-10
hi,

1234
1234
1234
1234
1234
1234

convert to
1234;1234;1234;1234;1234;

in excel

thanks in advance
0
Comment
Question by:Mandy_
6 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 40250357
pls clarify all you need in one Cell or in different cells?
0
 
LVL 13

Expert Comment

by:akb
ID: 40250361
Highlight the cells you want to transpose.
Copy them (Ctrl-C)
Then right click
Click on Paste Special
Tick the Transpose check box
Click OK
0
 
LVL 5

Expert Comment

by:NARANTHIRAN
ID: 40250362
Use the follwoing function to Concatenate cell range seperated with ";"

Function Concat(rng As Range, Optional sep As String = ",") As String
    Dim rngCell As Range
    Dim strResult As String
    For Each rngCell In rng
        If rngCell.Value <> "" Then
            strResult = strResult & sep & rngCell.Value
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(sep) + 1)
    End If
    Concat = strResult
End Function
concat.xlsm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Naresh Patel
ID: 40250363
or try this ..see attached
EE.xlsx
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40250365
thats nice idea but there 2 ;; between. i want only one ;
0
 
LVL 5

Accepted Solution

by:
NARANTHIRAN earned 180 total points
ID: 40250371
Hi Mandy
               just altert the function..
Function Concat(rng As Range, Optional sep As String = ";") As String
    Dim rngCell As Range
    Dim strResult As String
    For Each rngCell In rng
        If rngCell.Value <> "" Then
            strResult = strResult & sep & rngCell.Value
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(sep) + 1)
    End If
    Concat = strResult
End Function

PFA attachment in my previous post..
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question