View Full Version : Text strip send

07-02-2009, 05:15 AM
Hi Guys,

I'm after some help and don't know if this can be done via VBA

Basically I have a string of data in cell A6 in which I want to search and pull a certain section of this data in a cell and move it to another worksheet

Example of text string would be 37 characters long as per below

Test help me11: 01234567899.help.me23

I want to be able to only send the 1234567899 to the alternate worksheet and strip the other bit of information out.

Any help would be a massive help

RwNum = 1
ColNum = ColNum + 1
For Each myCell In Sh.Range("A6:CC6") 'Cell moving data from
RwNum = RwNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell '<-------------------END

Bob Phillips
07-02-2009, 05:27 AM
What determines what is stripped off? AS given it would just be

Newsh.Cells(RwNum, ColNum).Value = Mid$(myCell, 2, 9)

07-02-2009, 05:58 AM
Sorry should of mentioned that, basically anything after the 0 which will be generic and ends with a . at the last digit, it will always be a 10 digit number in the same location each time.

Bob Phillips
07-02-2009, 06:47 AM
The 0 will always be first or anywhere? So the nine digits after the 0?

07-02-2009, 07:09 AM
Yeah. Thanks for your help as I have got it all working thanks to the information you gave above, thanks heaps mate.

With the line of config you listed

Newsh.Cells(RwNum, ColNum).Value = Mid$(myCell, 2, 9)
How would I go about placing a colour highlight string into that as well, for instances if something was 2.1.1 which is ok leave and anything outside of that formation is marked in red?

Bob Phillips
07-02-2009, 08:12 AM
Lookup characters in VBA help, that shows you how to format individual characters within text within a cell.