How to Parse
I have a spreadsheet that has a column with alpha-numeric chars. Excel 2007.
ie: W -160 or L 136
The format is '1 alpha char ; space ; 3 numerical chars (could be +/-)
I would like to separate them into 2 columns.
1 column would be ALPHA and the other would be the numerical.
ie:
column 1 contains W
column 2 contains -160
Does someone know how to do that? Can they help me?
Extracting Data
Say, you have your Data in Column A beginning from Row 2.
Say in A2 you have W -160
and in A3 you have L 136
In B2 write this.
=LEFT(A2,1)
You will extract the Alphabet W in B2.
Copy B2 and paste it in B3.
You will extract the Alphabet L in B3.
In C2 write this.
=TRIM(MID(A2,2,LEN(A2)-1))
You will extract -160 in C2.
Copy C2 and paste it in C3.
You will extract 136 in C3.
If you have the formula directly in the sheet, in case if you change the data in column A, then your results in Column B & C will change.
As an Alternative, you can copy the results in Col B & C and using Paste Special you can paste only the result excluding the formula in Col D & E.
If you want this in VBA, if no one guides you, then I can suggest.
Thx a ton. That helped alot.
Thx a ton. That helped alot.
Good
Glad to know that my reply helped you.