-2

How can I break up text within a cell with vba? I exported emails to an excel file using a vba and the information exported in one of the cells is formatted as seen below:

Name * xxxxxx
Country of residence * xxxxxx Email * xxxxx@gmail.com mailto:xxxxxxx@gmail.com
Mobile phone number * 0xxxxxx
Do you want to become a member of Assoc? Yes Check all that apply *

Members
Education
Ethical Conduct
Events
Regulation

I tried the solution below and it’s not working. From article: If you need to build a formula to remove these line breaks all you need to know is that this ‘character’ is character 10 in Excel. You can create this character in an Excel cell with the formula =CHAR(10).

So to remove it we can use the SUBSTITUTE formula and replace CHAR(10) with nothing ( shown as “”).

https://www.auditexcel.co.za/blog/removing-line-breaks-from-cells-the-alt-enters/#:~:text=Building%20a%20formula%20to%20remove%20the%20ALT%20ENTER%20line%20breaks,-If%20you%20need&text=You%20can%20create%20this%20character,cell%20with%20no%20line%20breaks.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
MWanderlust
  • 1
  • 1
  • 2
    It would be useful to know what the expected result is, also, please include the solution you tried as part of the question, otherwise, no one will know what you are talking about if the provided link goes dead. Can you also specify what the problem you encountered is? – cybernetic.nomad Jan 26 '22 at 18:48
  • Do you want to [insert line breaks](https://stackoverflow.com/questions/9900916/insert-line-break-in-wrapped-cell-via-code)? Or do you want to [remove line breaks](https://stackoverflow.com/questions/10024402/how-to-remove-line-break-in-string)? – Christofer Weber Jan 26 '22 at 18:56

1 Answers1

0

My understanding is that you dump an email into 1 excel cell and are hoping to separate a series of strings [Country, Email, Etc.] that are separated by a line break?

I suggest using the split function to separate the strings into an array, then loop through that array to put the information in the desired cells. Mind you this will only work if the items are in the same order everytime, if the order can change then you will need to add a data verification step. i.e. if inStr("@",[Range]) then its an email...

Split([string to split], [delimiter]) https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function

Dim strEmail as String 'Email dump
Dim arrEmail() as String 'Array for looping
Dim ItemsInArray as Integer 'Used to hold array count
Dim i as Integer 'Counter

strEmail = ActiveSheet.Cells("[Column,Row]") 'Cell your email dumps to
arrEmail = Split(strEmail, char(10)) 'Populate array
ItemsInArray = UBound(arrEmail) 'Get upper bound of array (total item count)

For i = 0 to ItemsInArray
    ActiveSheet.Cells("[Column,Row]") = arrEmail(i)
    Column + 1
Next i

when i = 0 its a country code
when i = 1 its an email
when i = 2 its a phone #
etc....

SamwiseVB
  • 51
  • 8
  • exactly that! I want to split the text! I’ll upload a picture of what it looks like and what I’d like to see. Basically I keep a log of items received into an inbox. I log the date, sender, body – MWanderlust Jan 28 '22 at 11:36
  • the best way to see what is going on is to use the watch and immediate window. so highlight the variable you want to watch, right click, "add watch" and select break when value changes. Then the immediate window is an amazing tool for changing variables while code is running. debug.print [whataever you want to see] will display it in the immediate window but change the running code. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/use-the-immediate-window – SamwiseVB Jan 28 '22 at 17:18