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:
[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]04 79 12 09 00
06 47 86 78 99

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

 

[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:
[TABLE="width: 103"]
<TBODY>[TR]
[TD]04 79 12 09 00
06 47 86 78 99

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

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

[TABLE="width: 219"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]TÉL[/TD]
[TD]VILLE[/TD]
[/TR]
[TR]
[TD]06 67 58 90 70[/TD]
[TD]PARIS[/TD]
[/TR]
[TR]
[TD]06 75 42 53 36[/TD]
[TD]MARSEILLE[/TD]
[/TR]
[TR]
[TD]06 62 71 18 24 [/TD]
[TD]LE PERREUX-SUR-MARNE[/TD]
[/TR]
[TR]
[TD]06 08 74 79 55
07 51 22 02 15[/TD]
[TD]PARIS[/TD]
[/TR]
[TR]
[TD]06 07 18 48 48[/TD]
[TD]ST ESTEBEN[/TD]
[/TR]
[TR]
[TD]06 66 40 04 80[/TD]
[TD]MAUGUIO CARNON[/TD]
[/TR]
</tbody>[/TABLE]


Thanks
Steph[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]

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