Text to Columns Code
I need to write code for Text to Columns for the cells I select and they
would always be in the same column, but could be any column. Each cell contains one or more underscores, but I need it split up at the second underscore. I don't have a clue on how to write this, can you help? Example starting with would be: First-Set-Of-Words_SecondSet_ThirdSet I need: First Column Second Column First-Set-Of-Words_SecondSet ThirdSet I need this split between the second and third set dropping the second underscore too and to remain as text format. |
Text to Columns Code
MyWord = "First-Set-Of-Words_SecondSet_ThirdSet"
First_Underscore = InStr(MyWord, "_") Second_Underscore = InStr(First_Underscore + 1, MyWord, "_") FirstWord = Left(MyWord, Second_Underscore - 1) SecondWord = Mid(MyWord, Second_Underscore + 1) Range("A1") = FirstWord Range("B1") = SecondWord "Sheryl" wrote: I need to write code for Text to Columns for the cells I select and they would always be in the same column, but could be any column. Each cell contains one or more underscores, but I need it split up at the second underscore. I don't have a clue on how to write this, can you help? Example starting with would be: First-Set-Of-Words_SecondSet_ThirdSet I need: First Column Second Column First-Set-Of-Words_SecondSet ThirdSet I need this split between the second and third set dropping the second underscore too and to remain as text format. |
Text to Columns Code
Here is a more complete solution
Sub fixcolumn() firstcol = "D:D" For Each cell In Columns(firstcol) If cell < "" Then First_Underscore = InStr(cell, "_") Second_Underscore = InStr(First_Underscore + 1, cell, "_") If Second_Underscore 0 Then FirstWord = Left(cell, Second_Underscore - 1) SecondWord = Mid(cell, Second_Underscore + 1) cell = FirstWord cell.Offset(0, 1) = SecondWord End If End If Next cell "Sheryl" wrote: I need to write code for Text to Columns for the cells I select and they would always be in the same column, but could be any column. Each cell contains one or more underscores, but I need it split up at the second underscore. I don't have a clue on how to write this, can you help? Example starting with would be: First-Set-Of-Words_SecondSet_ThirdSet I need: First Column Second Column First-Set-Of-Words_SecondSet ThirdSet I need this split between the second and third set dropping the second underscore too and to remain as text format. |
Text to Columns Code
Wow, I don't think I had a chance to blink before you responded. Awesome
response time. My code is stopping at the If cell < "" Then I haven't messed with much code lately and don't know where to begin to get this to run. Am I missing something I need to add? "Joel" wrote: Here is a more complete solution Sub fixcolumn() firstcol = "D:D" For Each cell In Columns(firstcol) If cell < "" Then First_Underscore = InStr(cell, "_") Second_Underscore = InStr(First_Underscore + 1, cell, "_") If Second_Underscore 0 Then FirstWord = Left(cell, Second_Underscore - 1) SecondWord = Mid(cell, Second_Underscore + 1) cell = FirstWord cell.Offset(0, 1) = SecondWord End If End If Next cell "Sheryl" wrote: I need to write code for Text to Columns for the cells I select and they would always be in the same column, but could be any column. Each cell contains one or more underscores, but I need it split up at the second underscore. I don't have a clue on how to write this, can you help? Example starting with would be: First-Set-Of-Words_SecondSet_ThirdSet I need: First Column Second Column First-Set-Of-Words_SecondSet ThirdSet I need this split between the second and third set dropping the second underscore too and to remain as text format. |
Text to Columns Code
The fix is easy
from For Each cell In Columns(firstcol) to For Each cell In Range(firstcol) "Sheryl" wrote: Wow, I don't think I had a chance to blink before you responded. Awesome response time. My code is stopping at the If cell < "" Then I haven't messed with much code lately and don't know where to begin to get this to run. Am I missing something I need to add? "Joel" wrote: Here is a more complete solution Sub fixcolumn() firstcol = "D:D" For Each cell In Columns(firstcol) If cell < "" Then First_Underscore = InStr(cell, "_") Second_Underscore = InStr(First_Underscore + 1, cell, "_") If Second_Underscore 0 Then FirstWord = Left(cell, Second_Underscore - 1) SecondWord = Mid(cell, Second_Underscore + 1) cell = FirstWord cell.Offset(0, 1) = SecondWord End If End If Next cell "Sheryl" wrote: I need to write code for Text to Columns for the cells I select and they would always be in the same column, but could be any column. Each cell contains one or more underscores, but I need it split up at the second underscore. I don't have a clue on how to write this, can you help? Example starting with would be: First-Set-Of-Words_SecondSet_ThirdSet I need: First Column Second Column First-Set-Of-Words_SecondSet ThirdSet I need this split between the second and third set dropping the second underscore too and to remain as text format. |
Text to Columns Code
You didn't specify the column so I used D:D. Change this as necessary
"Sheryl" wrote: Wow, I don't think I had a chance to blink before you responded. Awesome response time. My code is stopping at the If cell < "" Then I haven't messed with much code lately and don't know where to begin to get this to run. Am I missing something I need to add? "Joel" wrote: Here is a more complete solution Sub fixcolumn() firstcol = "D:D" For Each cell In Columns(firstcol) If cell < "" Then First_Underscore = InStr(cell, "_") Second_Underscore = InStr(First_Underscore + 1, cell, "_") If Second_Underscore 0 Then FirstWord = Left(cell, Second_Underscore - 1) SecondWord = Mid(cell, Second_Underscore + 1) cell = FirstWord cell.Offset(0, 1) = SecondWord End If End If Next cell "Sheryl" wrote: I need to write code for Text to Columns for the cells I select and they would always be in the same column, but could be any column. Each cell contains one or more underscores, but I need it split up at the second underscore. I don't have a clue on how to write this, can you help? Example starting with would be: First-Set-Of-Words_SecondSet_ThirdSet I need: First Column Second Column First-Set-Of-Words_SecondSet ThirdSet I need this split between the second and third set dropping the second underscore too and to remain as text format. |
All times are GMT +1. The time now is 10:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com