Split & Trim question
 

Sample data:

| col B | col C | col D | col E | col F |
10 0. 88 0.3 4 m2 1 0.22
needs to become
10 0.88 0.34 m2 10.22

I use a test on col F to find suitable rows.
In the case of the col B cell, I need to keep the '10', but move
the rest of cell's contents into the start of the adjacent cell (same
row) so:

Dim C As Range, x As Variant
With ActiveSheet
For Each C In Intersect(Range("F:F"), ActiveSheet.UsedRange)
If Not IsEmpty(C) Then
If Not C.Value = "Unit" Then
x = Split(C.Offset(0, -4), " ")
If UBound(x) 0 Then 'there is data to be moved
C.Offset(0, -3).Value = x(1) & "." &
C.Offset(0, -3).Value
C.Offset(0, -4).Value = x(0)

So col C should now be: 0. 88 0.3

I've deliberately added to col C by inserting a space. If col C originally
began with a number (88) then I don't need the space....but if it's a text
value then I need the space....How do I test this please?

In the above example, the original col B contents could contain several
elements, separated by one or many spaces.

How do I move all the Split values apart from x(0) into col C, and
then with col C, remove all spaces (except one) between the "strings"?

Obviously this will replicate across cols D-F.

Any help much appreciated.

Regards




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004

Split & Trim question
 

Possibly:

sStr = "0. 88"

iloc = Instr(sStr," ")
if iloc 0 the
sStr1 = Left(sStr,iloc-1)
else
sStr1 = sStr
End if
if isnumeric(sStr1)

--
Regards,
Tom Ogilvy



"Stuart" wrote in message
...
Sample data:

| col B | col C | col D | col E | col F |
10 0. 88 0.3 4 m2 1 0.22
needs to become
10 0.88 0.34 m2 10.22

I use a test on col F to find suitable rows.
In the case of the col B cell, I need to keep the '10', but move
the rest of cell's contents into the start of the adjacent cell (same
row) so:

Dim C As Range, x As Variant
With ActiveSheet
For Each C In Intersect(Range("F:F"), ActiveSheet.UsedRange)
If Not IsEmpty(C) Then
If Not C.Value = "Unit" Then
x = Split(C.Offset(0, -4), " ")
If UBound(x) 0 Then 'there is data to be moved
C.Offset(0, -3).Value = x(1) & "." &
C.Offset(0, -3).Value
C.Offset(0, -4).Value = x(0)

So col C should now be: 0. 88 0.3

I've deliberately added to col C by inserting a space. If col C originally
began with a number (88) then I don't need the space....but if it's a text
value then I need the space....How do I test this please?

In the above example, the original col B contents could contain several
elements, separated by one or many spaces.

How do I move all the Split values apart from x(0) into col C, and
then with col C, remove all spaces (except one) between the "strings"?

Obviously this will replicate across cols D-F.

Any help much appreciated.

Regards




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004