Does anyone know a macro to convert text numbers to real numbers for example in cell A2: CH.000’266’689’8 to CH0002666898 and how to partial delete the text in cell B2 and convert the text numbers to real numbers: Current: SHS -49’553.647 to 49553647 and delete the rows between the CH000… security ID(there are always 8 rows between the security ID). Do this for the whole sheet containing a lot of balance positions. Any help is highy appreciated Col A-----------------Col B Security------------Positions CH.000’266’689’8—Current: SHS -49’553.647 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxxx CH.000’233’570’3—Current: SHS -46’205.371 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxxx CH.000’279’222’9----Current: SHS -325’100.092 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxxx
I found the easiest way to write position-based macros was to just know how to use excel without the mouse. Just use the record macro function with a non-dependant/neutral cell selected as the starting point. The for convert to numbers -> ctrl+1 etc… Deleting Cell B2 -> CTRL+ (left arrow) + CTRL+ (up arrow)+(right arrow)+ (dow arrow)+‘delete’
If I am following you correctly this should work. Assuming the values in column A are always the same lengh you can use this: =LEFT(A2,2)&MID(A2,4,3)&MID(A2,8,3)&MID(A2,12,3)&RIGHT(A2,1) For column B insert a VBA module and paste this in: Function NumOnly(txt As String) As String With CreateObject(“VBScript.RegExp”) .Pattern = “[^\d]” .Global = True If .Test(txt) Then NumOnly = .Replace(txt, “”) End With End Function Now you can use: =numonly(B2) Fill the formulas so it skips every seven rows. Create a table, unclick blank cells and now you have your data without anything in between.
ASSet_MANagement and Howd thank you very much for helping me out. Do you happen to know a macro that delete all rows if there’s a specific text such as “Current: SHS” within the rows?
Apparently Thanh1977 thinks AF is his private source of 24/7 Excel support.