Convert text to column and transpose

J

jitendra

Board Regular
Joined
Aug 31, 2011
Messages
92
Hi friend,

I have some data like below and I want to change it like the second table by using macro if anyone know please help me and thanks in advance.

Existing data
Country Destination C C Cde Price($) statu
ALBANIA OLO 355 4249,4250,4251,4252 3.5 new
ALBANIA TIRANA 126 422,423,424 6.8 old

Required data
Country Destination Country Code City Code Price($) statu
ALBANIA OLO 355 4249 3.5 new
ALBANIA OLO 355 4250 3.5 new
ALBANIA OLO 355 4251 3.5 new
ALBANIA OLO 355 4252 3.5 new
ALBANIA TIRANA 126 422 6.8 old
ALBANIA TIRANA 126 423 6.8 old
ALBANIA TIRANA 126 424 6.8 old


My required format is
Column 1:-Country
Column 2:- Destination
Column 3:-Country Code
Column 4:-City Code
Column 5:-Price($)
Column 6:-Status
 
Sort by date Sort by votes
Biz,

Yeah, my code assumes that Column D contains text values.

Hiker,

Thanks!

Cheers to you both!
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hello - how do we make it run on all cells. Right now it only converts the text and transposes one column. Thank you!
 
Upvote 0
Venkatp said:
Hello - how do we make it run on all cells. Right now it only converts the text and transposes one column. Thank you!
Click to expand...

Venkatp,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0
hiker95 said:
See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.
Click to expand...
You should read all of hiker95's comments, but to follow up on the above part of his comments... if the number of delimited pieces of text can vary from cell to cell, be sure to show that variation and the results for it so we can see how, if at all, you want the "alignment" between delimited pieces of text to be arranged.
 
Last edited:
Upvote 0
Thank you hiker95 and Rick for the reply. My data is as below:

Actual data:
Databefore transformation
[TABLE="width: 1"]
<tbody>[TR]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Key[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Nick Name[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Lead name[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Member Name[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Gender[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Age[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Status[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Trans Mode[/FONT]​
[/TD]
[/TR]
[TR]
[TD] y9gbb
[/TD]
[TD]
[/TD]
[TD] Venu
[/TD]
[TD] Venu G, Mantina, Prahlad, Gopal
[/TD]
[TD] Male, Female, Male, Male
[/TD]
[TD] 2, 7, 44, 37
[/TD]
[TD] Tentative, Tentative, Tentative, Tentative
[/TD]
[TD] Bus
[/TD]
[/TR]
[TR]
[TD] 38p98
[/TD]
[TD] vdas
[/TD]
[TD] Ven
[/TD]
[TD] Ven, Sumi, Naid P, Rama, Prem
[/TD]
[TD] Male, Female, Male, Female, Female
[/TD]
[TD] 35, 35, 60, 50, 75
[/TD]
[TD] Confirmed, Confirmed, Confirmed, Confirmed, Tentative
[/TD]
[TD] Bus
[/TD]
[/TR]
</tbody>[/TABLE]


Goal:
[TABLE="width: 1"]
<tbody>[TR]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Key[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Nick Name[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Lead name[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Member Name[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Gender[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Age[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Status[/FONT]​
[/TD]
[TD="bgcolor: #66FFFF"]
[FONT=&quot]Trans Mode[/FONT]​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"] y9gbb
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"] Venu
[/TD]
[TD="bgcolor: #E2EFDA"] Venu G
[/TD]
[TD="bgcolor: #E2EFDA"] Male
[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD="bgcolor: #E2EFDA"] Tentative
[/TD]
[TD="bgcolor: #E2EFDA"] Bus
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] Mantina
[/TD]
[TD] Female
[/TD]
[TD]
7​
[/TD]
[TD] Tentative
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"] Prahlad
[/TD]
[TD="bgcolor: #E2EFDA"] Male
[/TD]
[TD="bgcolor: #E2EFDA"]
44​
[/TD]
[TD="bgcolor: #E2EFDA"] Tentative
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] Gopal
[/TD]
[TD] Male
[/TD]
[TD]
37​
[/TD]
[TD] Tentative
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"] 38p98
[/TD]
[TD="bgcolor: #E2EFDA"] Vdas
[/TD]
[TD="bgcolor: #E2EFDA"] Ven
[/TD]
[TD="bgcolor: #E2EFDA"] Ven
[/TD]
[TD="bgcolor: #E2EFDA"] Male
[/TD]
[TD="bgcolor: #E2EFDA"]
35​
[/TD]
[TD="bgcolor: #E2EFDA"] Confirmed
[/TD]
[TD="bgcolor: #E2EFDA"] Bus
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] Sumi
[/TD]
[TD] Female
[/TD]
[TD]
35​
[/TD]
[TD] Confirmed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"] Naid P
[/TD]
[TD="bgcolor: #E2EFDA"] Male
[/TD]
[TD="bgcolor: #E2EFDA"]
60​
[/TD]
[TD="bgcolor: #E2EFDA"] Confirmed
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] Rama
[/TD]
[TD] Female
[/TD]
[TD]
50​
[/TD]
[TD] Confirmed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[TD="bgcolor: #E2EFDA"] Prem
[/TD]
[TD="bgcolor: #E2EFDA"] Female
[/TD]
[TD="bgcolor: #E2EFDA"]
75​
[/TD]
[TD="bgcolor: #E2EFDA"] Tentative
[/TD]
[TD="bgcolor: #E2EFDA"]
[/TD]
[/TR]
</tbody>[/TABLE]


The delimiter is Fixed i.e, its "," but the column data is variant and the problem I get when I run the macro from previous posts (by hiker95 posted Aug 31st, 2011, 09:30 PM ) is that only Member Name is transformed and Gender, Age.. columns still stay the same.

I hope I am clear. Please let me know if more details are needed. (Using Excel version 2016)
 
Upvote 0
Venkatp,

Here is a macro solution for you to consider that is based on your latest screenshots. I assume that the cells in each group in a row, in columns D, E, F, and, G, contain the same number of separator characters, the comma and a space.

Sample raw data in the active worksheet:


Excel 2007
ABCDEFGH
1KeyNick NameLead nameMember NameGenderAgeStatusTrans Mode
2y9gbbVenuVenu G, Mantina, Prahlad, GopalMale, Female, Male, Male2, 7, 44, 37Tentative, Tentative, Tentative, TentativeBus
338p98vdasVenVen, Sumi, Naid P, Rama, PremMale, Female, Male, Female, Female35, 35, 60, 50, 75Confirmed, Confirmed, Confirmed, Confirmed, TentativeBus
4
5
6
7
8
9
10
11
Sheet1


And, after the macro:


Excel 2007
ABCDEFGH
1KeyNick NameLead nameMember NameGenderAgeStatusTrans Mode
2y9gbbVenuVenu GMale2TentativeBus
3MantinaFemale7Tentative
4PrahladMale44Tentative
5GopalMale37Tentative
638p98vdasVenVenMale35ConfirmedBus
7SumiFemale35Confirmed
8Naid PMale60Confirmed
9RamaFemale50Confirmed
10PremFemale75Tentative
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorganizeData()
' hiker95, 06/12/2017, ME575713
Dim r As Long, lr As Long, c As Long, s, i As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    For c = 4 To 7 Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If c = 4 Then
          .Rows(r + 1).Resize(UBound(s)).Insert
        End If
        .Cells(r, c).Resize(UBound(s) + 1) = Application.Transpose(s)
      End If
    Next c
  Next r
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorganizeData macro.
 
Last edited:
Upvote 0
Thank you so much !!! It really saved a ton of time. Thank you!

This is my full form, Could you please modify such that it can run on below table:

[TABLE="width: 1392"]
<colgroup><col span="13"><col><col span="3"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Key[/TD]
[TD]Nick Name[/TD]
[TD]Lead name[/TD]
[TD]Member Name[/TD]
[TD]Gender[/TD]
[TD]Age[/TD]
[TD]Status[/TD]
[TD]Trans Mode[/TD]
[TD]Pref 1[/TD]
[TD]Pref 2[/TD]
[TD]Select 1[/TD]
[TD]Select 2[/TD]
[TD]Volunteer[/TD]
[TD]Language[/TD]
[TD]Country Code[/TD]
[TD]Whatsapp[/TD]
[TD]Mobile[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]y9gbb[/TD]
[TD] [/TD]
[TD]Venu[/TD]
[TD]Venu G, Mantina, Prahlad, Gopal[/TD]
[TD]Male, Female, Male, Male[/TD]
[TD]2, 7, 44, 37[/TD]
[TD]Tentative, Tentative, Tentative, Tentative[/TD]
[TD]Bus[/TD]
[TD]3 Bed[/TD]
[TD]2 Bed[/TD]
[TD]One Start[/TD]
[TD]Two Start[/TD]
[TD]1 hours/day, .5 hours/day[/TD]
[TD]English, English, English, English[/TD]
[TD]001[/TD]
[TD]123-456-7890[/TD]
[TD]123-456-7890[/TD]
[TD]Allergies ….[/TD]
[/TR]
[TR]
[TD]38p98[/TD]
[TD]vdas[/TD]
[TD]Ven[/TD]
[TD]Ven, Sumi, Naid P, Rama, Prem[/TD]
[TD]Male, Female, Male, Female, Female[/TD]
[TD]35, 35, 60, 50, 75[/TD]
[TD]Confirmed, Confirmed, Confirmed, Confirmed, Tentative[/TD]
[TD]Bus[/TD]
[TD]2 Bed[/TD]
[TD]3 Bed[/TD]
[TD]Two Star[/TD]
[TD]One Star[/TD]
[TD].5 hours/day, .5 hours/day, 1 hours/day, 1 hours/day[/TD]
[TD]English, English, Telugu, Telugu, Tamil[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Convert text to column and transpose
Thank you so much !!! It really saved a ton of time. Thank you!
Click to expand...

Venkatp,

Thanks for the feedback.

You are very welcome. Glad I could help.
 
Upvote 0
This is my full form, Could you please modify such that it can run on below table:
Click to expand...

Venkatp,

I see that in a row, there could be a different number of comma/space characters, and, strings.

Be back in a little while.
 
Upvote 0
Venkatp,

Here is another macro for you to consider that is based on your latest screenshots.

Not all columns are shown to fit the MrExcel display area.

Sample raw data in the active worksheet:


Excel 2007
ABCDEFGHMNOPQR
1KeyNick NameLead nameMember NameGenderAgeStatusTrans ModeVolunteerLanguageCountry CodeWhatsappMobileComments
2y9gbbVenuVenu G, Mantina, Prahlad, GopalMale, Female, Male, Male2, 7, 44, 37Tentative, Tentative, Tentative, TentativeBus1 hours/day, .5 hours/dayEnglish, English, English, English1123-456-7890123-456-7890Allergies .
338p98vdasVenVen, Sumi, Naid P, Rama, PremMale, Female, Male, Female, Female35, 35, 60, 50, 75Confirmed, Confirmed, Confirmed, Confirmed, Tentative
4
Sheet1


And, after the new macro:


Excel 2007
ABCDEFGHMNOPQR
1KeyNick NameLead nameMember NameGenderAgeStatusTrans ModeVolunteerLanguageCountry CodeWhatsappMobileComments
2y9gbbVenuVenu GMale2TentativeBus1 hours/dayEnglish1123-456-7890123-456-7890Allergies .
3MantinaFemale7Tentative.5 hours/dayEnglish
4PrahladMale44TentativeEnglish
5GopalMale37TentativeEnglish
638p98vdasVenVenMale35Confirmed
7SumiFemale35Confirmed
8Naid PMale60Confirmed
9RamaFemale50Confirmed
10PremFemale75Tentative
11
Sheet1




Code: 
Sub ReorganizeData_V2()
' hiker95, 06/12/2017, ME575713
Dim r As Long, lr As Long, lc As Long, c As Long, s, i As Long, n As Long, mxr As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = lr To 2 Step -1
    mxr = 0
    For c = 4 To lc Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If UBound(s) > mxr Then
          mxr = UBound(s)
        End If
      End If
    Next c
    .Rows(r + 1).Resize(mxr).Insert
    For c = 4 To lc Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If UBound(s) < mxr Then
          .Cells(r, c).Resize(UBound(s) + 1) = Application.Transpose(s)
        Else
          .Cells(r, c).Resize(mxr + 1) = Application.Transpose(s)
        End If
      End If
    Next c
  Next r
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my Reply #16.

Then run the ReorganizeData_V2 macro.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
871
onlyadrafter
O
E
  • Question Question
Urgent Formula/Macros assitance
Replies
2
Views
493
Eugene Hirdes
E
J
  • Question Question
Update the new colum
Replies
2
Views
593
jitendra
J

Forum statistics

Threads
1,224,568
Messages
6,179,595
Members
452,927
Latest member
whitfieldcraig

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top