I have a large spread sheet pasted from elsewhere. Lines alternate.

In Cell A1,A3,A5... I have text I want to keep.
In Cells A2, A4, A6... I have a string of numbers: eg
6.580 6.590 6.500 -0.010 5.950 ........
but they may be bigger or smaller than this: eg
12.250 12.310 12.300 0.040 .........
or 0.720 0.730 0.725 0.010 ......
I want to take the third one of these numbers (eg 6.500, or 12.300, or
0.725), paste it as a number into cell B. of the row above, and then delete
the row that this number originally came from. This needs to be done as a
subroutine through the whole spreadsheet.

The final result will be text in column A, a corresponding single number in
column B, and with only half the number of lines I originally started with.

Thanks for any help.

I think that "FIND()" will help you.
try this:=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,FIND(" ",A2,FIND("
",A2,FIND(" ",A2)+1)+1)-FIND(" ",A2,FIND(" ",A2)+1))

"Kanga 85" wrote:

I have a large spread sheet pasted from elsewhere. Lines alternate.

In Cell A1,A3,A5... I have text I want to keep.
In Cells A2, A4, A6... I have a string of numbers: eg
6.580 6.590 6.500 -0.010 5.950 ........
but they may be bigger or smaller than this: eg
12.250 12.310 12.300 0.040 .........
or 0.720 0.730 0.725 0.010 ......
I want to take the third one of these numbers (eg 6.500, or 12.300, or
0.725), paste it as a number into cell B. of the row above, and then delete
the row that this number originally came from. This needs to be done as a
subroutine through the whole spreadsheet.

The final result will be text in column A, a corresponding single number in
column B, and with only half the number of lines I originally started with.

Thanks for any help.

You could use a macro to do this eg

Sub CleanUp()

Dim spaceOne, spaceTwo, spaceThree As Integer
Dim getNum As Double

Range("A1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Row Mod 2 = 0 Then
spaceOne = InStr(ActiveCell.Value, " ")
spaceTwo = InStr(spaceOne + 1, ActiveCell.Value, " ")
spaceThree = InStr(spaceTwo + 1, ActiveCell.Value, " ")
getNum = Mid(ActiveCell.Value, spaceTwo + 1, _
spaceThree - spaceTwo - 1)
ActiveCell.Offset(-1, 1).Value = getNum
End If
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(-1, 0).Select
Do Until ActiveCell.Row = 1
If ActiveCell.Row Mod 2 = 0 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop

End Sub

Regards
Rowan

"Kanga 85" wrote:

I have a large spread sheet pasted from elsewhere. Lines alternate.

In Cell A1,A3,A5... I have text I want to keep.
In Cells A2, A4, A6... I have a string of numbers: eg
6.580 6.590 6.500 -0.010 5.950 ........
but they may be bigger or smaller than this: eg
12.250 12.310 12.300 0.040 .........
or 0.720 0.730 0.725 0.010 ......
I want to take the third one of these numbers (eg 6.500, or 12.300, or
0.725), paste it as a number into cell B. of the row above, and then delete
the row that this number originally came from. This needs to be done as a
subroutine through the whole spreadsheet.

The final result will be text in column A, a corresponding single number in
column B, and with only half the number of lines I originally started with.

Thanks for any help.

Thanks Rowan and ww. Looks exactly what I need.

"Rowan" wrote:

You could use a macro to do this eg

Sub CleanUp()

Dim spaceOne, spaceTwo, spaceThree As Integer
Dim getNum As Double

Range("A1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Row Mod 2 = 0 Then
spaceOne = InStr(ActiveCell.Value, " ")
spaceTwo = InStr(spaceOne + 1, ActiveCell.Value, " ")
spaceThree = InStr(spaceTwo + 1, ActiveCell.Value, " ")
getNum = Mid(ActiveCell.Value, spaceTwo + 1, _
spaceThree - spaceTwo - 1)
ActiveCell.Offset(-1, 1).Value = getNum
End If
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(-1, 0).Select
Do Until ActiveCell.Row = 1
If ActiveCell.Row Mod 2 = 0 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop

End Sub

Regards
Rowan

"Kanga 85" wrote:

I have a large spread sheet pasted from elsewhere. Lines alternate.

In Cell A1,A3,A5... I have text I want to keep.
In Cells A2, A4, A6... I have a string of numbers: eg
6.580 6.590 6.500 -0.010 5.950 ........
but they may be bigger or smaller than this: eg
12.250 12.310 12.300 0.040 .........
or 0.720 0.730 0.725 0.010 ......
I want to take the third one of these numbers (eg 6.500, or 12.300, or
0.725), paste it as a number into cell B. of the row above, and then delete
the row that this number originally came from. This needs to be done as a
subroutine through the whole spreadsheet.

The final result will be text in column A, a corresponding single number in
column B, and with only half the number of lines I originally started with.

Thanks for any help.