Read the text in a single cell line by line.

**moon** · 02-14-2010, 04:49 AM

Hi All,

Can anyone tell if there is a way to read the text in a single cell line by line using VBA?

E.g. In a multiple line cell (written using Alt+Enter) containing this:

Paul Smith
22, Elk Street
Smalltown

I would like to use VBA and read the strings from the 3 lines in the cell into 3 different variables.

Many thanks!

**DonkeyOte** · 02-14-2010, 04:56 AM

Assuming you've used Char(10) to add the line break then you can use Split accordingly, eg:

Dim vStrings As Variant, strOne As String, strTwo As String, strThree As String
vStrings = Split(Range("A1").Value,Chr(10))
strOne = vStrings(0)
strTwo = vStrings(1)
strThree = vStrings(2)

It follows that string variables may be moot - ie you could simply refer to vStrings(n) as and when required.