Split cell into individual cells

[FryGirl]

Hello All,

I have some text in four cells separated within the cells by Week. How can I get each Week + text separated into its own cell in a column format? A1:A4 is what I'm starting with with the results in column C. In essence, there will be 13 weeks per cell in column A to make a year, 52 weeks.

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Week 1 - Text text text Week 2 - Text text text​

[/TD]
[TD][/TD]
[TD]

Week 1 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Week 3 - Text text text Week 4 - Text text text​

[/TD]
[TD][/TD]
[TD]

Week 2 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Week 5 - Text text text Week 6 - Text text text​

[/TD]
[TD][/TD]
[TD]

Week 3 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Week 7 - Text text text Week 8 - Text text text​

[/TD]
[TD][/TD]
[TD]

Week 4 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Week 5 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Week 6 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Week 7 - Text text text​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Week 8 - Text text text​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

perhaps post a more representative sample?

 

[Rick Rothstein]

I have some text in four cells separated within the cells by Week. How can I get each Week + text separated into its own cell in a column format? A1:A4 is what I'm starting with with the results in column C. In essence, there will be 13 weeks per cell in column A to make a year, 52 weeks.

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub SplitWeekDataOut()
  Dim LastRow As Long, Weeks As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Weeks = Split(Mid(Join(Application.Transpose(Evaluate(Replace("IF(A1:A#="""","""",SUBSTITUTE("" ""&A1:A#,"" Week""," & """|Week""))", "#", LastRow))), ""), 2), "|")
  Columns("C").ClearContents
  Range("C1").Resize(UBound(Weeks) + 1) = Application.Transpose(Weeks)
End Sub[/td]
[/tr]
[/table]

 

[FryGirl]

Hi Rick and thank you. On the test data this worked fine and I would have thought my mock-up would have been no different, but with the below I get "Type Mismatch" and the debug points to the Split function. I pasted two rows as that would be representative.

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]

Week 1 – “Your word is a lamp to my feet and a light for my path.” – Psalm 119:105, NIV Week 2 – “Trust in the LORD with all your heart and lean not on your own understanding.” - Proverbs 3:5, NIV Week 3 – “Pray without ceasing.” – 1 Thessalonians 5:17, KJV Week 4 – “Therefore, if anyone is in Christ, he is a new creation; the old has gone, the new has come!” – 2 Corinthians 5:17, NIV Week 5 – “Be on your guard; stand firm in the faith; be courageous; be strong.” - 1 Corinthians 16:13, NIV Week 6 – “And whatever you do, do it heartily, as to the Lord and not to men.” - Colossians 3:23, NKJV Week 7 – “For where your treasure is, there will your heart be also.” – Luke 12:34, KJV Week 8 – “I can do all things through Christ who strengthens me.” – Philippians 4:13, NKJV Week 9 – “My God shall supply all your need according to his riches in glory by Christ Jesus. - Philippians 4:19, KJV Week 10 – “For I know the plans I have for you,” declares the LORD, “plans to prosper you and not to harm you, plans to give you hope and a future.” – Jeremiah 29:11, NIV Week 11 – “You keep him in perfect peace whose mind is stayed on you, because he trusts in you.” – Isaiah 26:3, ESV Week 12 – “If any of you lacks wisdom, let him ask God, who gives generously to all without reproach, and it will be given him.”- James 1:5, ESV Week 13 – “Oh that men would praise the LORD for his goodness, and for his wonderful works to the children of men!”- Psalm 107:8, KJV​

[/td][/tr]


[tr][td]

2​

[/td][td]

Week 14 – “For he satisfies the thirsty and fills the hungry with good things.” – Psalm 107:9, NIV Week 15 – “ Do not be deceived: God cannot be mocked. A man reaps what he sows.” - Galatians 6:7, NIV Week 16 – “The LORD is my shepherd; I shall not want.” – Psalm 23:1, ESV Week 17 – “Surely goodness and mercy shall follow me all the days of my life, and I shall dwell in the house of the LORD forever.” – Psalm 23:6, ESV Week 18 – “And we know that all things work together for good to them that love God, to them who are the called according to his purpose.” – Romans 8:28, KJV Week 19 – “What shall we then say to these things? If God be for us, who can be against us?” - Romans 8:31, KJV Week 20 – “In every thing give thanks: for this is the will of God in Christ Jesus concerning you.” - 1 Thessalonians 5:18, KJV Week 21 – “For God hath not given us the spirit of fear; but of power, and of love, and of a sound mind.” – 2 Timothy 1:7, KJV Week 22 – “Jesus answered, “I am the way and the truth and the life. No one comes to the Father except through me.” – John 14:6, ESV Week 23 – “For all have sinned and fall short of the glory of God.” – Romans 3:23, NIV Week 24 – “For by grace you have been saved through faith. And this is not your own doing; it is the gift of God.” – Ephesians 2:8, ESV Week 25 – “I praise you because I am fearfully and wonderfully made; your works are wonderful,I know that full well.” – Psalm 139:14, NIV​

[/td][/tr]
[/table]

 

[Rick Rothstein]

Hi Rick and thank you. On the test data this worked fine and I would have thought my mock-up would have been no different, but with the below I get "Type Mismatch" and the debug points to the Split function. I pasted two rows as that would be representative.

Actually, the problem is with the Evaluate function which has a limit as to how much data it can handle (your cells have over 1400 characters each in them). Here is what I post for situations like this... "please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout".

With that said, see if this macro works with your data...

[table="width: 500"]
[tr]
	[td]Sub SplitWeekDataOut()
  Dim LastRow As Long, Weeks As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Weeks = Split(Mid(Replace(" " & Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), "|"), " Week", "|Week"), 2), "|")
  Range("C1").Resize(UBound(Weeks) + 1) = Application.Transpose(Weeks)
End Sub[/td]
[/tr]
[/table]

 

[FryGirl]

"please do not simplify your question for us"

Words well taken and understood.

With that said, see if this macro works with your data...

Yes, that worked fine with the full data set.

Thank you for your time.