manipulate text, must be simple :)

[rplim2016]

Hello Excel Guru's,

Simple problem in VBA:

code:
x = range("A1").value 'example, the value of A1 is 'Source DATA'!, the value of A1 is variable but always contain ' & '! in the beginning and end


question:
how can I modify the x inside the VBA, is there a way to make the x = Source DATA using text manipulation inside the VBA? like mid, len, search.. so I can customize it.

thanks a lot!

 

[sheetspread]

With the replace function change ' and ! to ""

 

[rplim2016]

With the replace function change ' and ! to ""

How please?

 

[Rick Rothstein]

the value of A1 is 'Source DATA'!

When you look at the cell (not the Formula Bar) the above value is in, do you see the leading apostrophe or not?

 

[rplim2016]

When you look at the cell (not the Formula Bar) the above value is in, do you see the leading apostrophe or not?

Hello Rick, the formula for A1 is ="'Source DATA'!" but the visible in spreadsheet is 'Source Data'!, how can we remove and retain the wordings only?

 

[Rick Rothstein]

Hello Rick, the formula for A1 is ="'Source DATA'!" but the visible in spreadsheet is 'Source Data'!, how can we remove and retain the wordings only?

Try this code line...

x = Mid(Range("A1").Value, 2, Len(Range("A1").Value) - 4)

 

[rplim2016]

works perfect! :D