parse data separately in cells with vbNewLine

[stephfr]

Hi

I need to extract data from an excel file (using VBA) to another file. However, the data is stored in one cell with a line break to separate each item.

I need to be able to read each string within the cell so that I can extract the mobile numbers (starting with 06 or 07). For the first number in the string, I'm OK (I can use the left function) but I need to be also able to read the second number that is after the NewLine.

When I create the original data in the cell (and add the new line, I do this: Tel1 & vbNewLine & Tel2). Now I need to be able to read each one individually.

Can anyone help???

Thanks
Steph


For example:

Cell G2:

04 79 12 09 00 06 47 86 78 99

<colgroup><col></colgroup><tbody>
</tbody>

 

[Gary's Student]

You can make an array that contains the individual sub-strings:

Sub dural()
Dim s As String
s = Range("G2").Value
ary = Split(s, vbNewLine)
End Sub

 

[Rick Rothstein]

Hi

I need to extract data from an excel file (using VBA) to another file. However, the data is stored in one cell with a line break to separate each item.

I need to be able to read each string within the cell so that I can extract the mobile numbers (starting with 06 or 07). For the first number in the string, I'm OK (I can use the left function) but I need to be also able to read the second number that is after the NewLine.

When I create the original data in the cell (and add the new line, I do this: Tel1 & vbNewLine & Tel2). Now I need to be able to read each one individually.

Can anyone help???

Thanks
Steph


For example:

Cell G2:

04 79 12 09 00 06 47 86 78 99

<TBODY>
</TBODY>

I am a little confused at what you want from G2... can you show us the result you expect to put in the new file?

Also, what is the new file... a text file, a worksheet, something else?

 

[stephfr]

I am a little confused at what you want from G2... can you show us the result you expect to put in the new file?

Also, what is the new file... a text file, a worksheet, something else?

Hi Rick

The new file is a worksheet (which I have no problem with).

My example, is column G contains a list of phone numbers for given contacts. When there are several phone numbers for the same contact, they are in the same cell separated by a vbNewLine.

What I need is to only extract the data where at least one of the telephone numbers begins with 06 or 07

Here is an extract of a column that I would expext in the result

TÉL	VILLE
06 67 58 90 70	PARIS
06 75 42 53 36	MARSEILLE
06 62 71 18 24	LE PERREUX-SUR-MARNE
06 08 74 79 55 07 51 22 02 15	PARIS
06 07 18 48 48	ST ESTEBEN
06 66 40 04 80	MAUGUIO CARNON

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks
Steph

<colgroup><col></colgroup><tbody>
</tbody>