Coppy and shortening and string

[ender462]

Hello,
I am fairly new with VBA and code has never been my strongpoint. I do research in a Nueroimmune Lab and I am working on a series of macros for exporting/manipulating large data sets from out flow cytometry individual panel reports (.xls) into one master worksheet. I have managed to accurately pool data into one workbook, but would like to change the notation on a specific column of data. The column reports cell concentration:

6.7x10^3/uL

<tbody>
</tbody>

I would like to be able to only keep the first 3 characters of the cell in string and loop through each cell saving these new values as a string. Any help would be greatly appreciated!

-Jared

 

[Scott Huish]

Since the first part of that string is a number, this should work and will allow for the possibility that your initial number might go over 3 characters.

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
c = Val(c)
Next
End Sub

 

[Rick Rothstein]

Since the first part of that string is a number, this should work and will allow for the possibility that your initial number might go over 3 characters.

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
c = Val(c)
Next
End Sub

If the OP's data in Column A always looks as he shows in his single example; namely, a number followed by an "x" followed by more text, then this would also work...

Sub Test2()
  Columns("A").Replace "x*", "", xlPart, , False, , False, False
End Sub

Of course, the OP could just as easily select Column A, press CTRL+H to bring up the Replace dialog box and do it manually as well.