Coppy and shortening and string

ender462

New Member
Joined
Jan 31, 2018
Messages
1
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:
[TABLE="width: 0"]
<tbody>[TR]
[TD]6.7x10^3/uL
[/TD]
[/TR]
</tbody>[/TABLE]
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

Code:
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
 
Upvote 0
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.

Code:
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...
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top