Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim text and split across three columns | Excel Discussion (Misc queries) | |||
Trim question | Excel Discussion (Misc queries) | |||
Split UPC Question | Excel Discussion (Misc queries) | |||
Another, sort of trim question | Excel Discussion (Misc queries) | |||
Right & Split question | Excel Programming |