About converting

[ganesang]

Hi

I need the another help in the excel.

I have values in the cell for ex., cell "J5" ->J1,J2 it should be converted in the another cell "S5" to,



<functionRef="J1">
<functionRef="J2">

if the cell contain one value only as J1 should be converted as "<functionRef="J1">"

Please advise on this......

[Pecoflyer]

Please do not hijack existing threads with a new question ( even your own threads) but create a new one eventually with a link to any post of interest.
I did it for you this time

[ganesang]

Hi Pecoflyer

Sorry for the inconvenience. I will make a note on this in future.

I will wait for the answer for this question.

Please let me know if any more clarifications needed.

Thanks
Ganesan. G

[ganesang]

Can anyone help me out on this?

I have explained detail as follows

I have the values which is separated by commas as follows,

for exam, Cell J5 contains J1 or J1,J2 or J1,D2,D3 and so on...

I need to convert this as in to the Cell S5 as,

<funcref="J1"/> or <funcref="J1"/><funcref="J2"/> or <funcref="J1"/><funcref="D2"/><funcref="D3"/> and so on....

Cell J5 sometime appears as empty. In this case retains as empty.

So whatever in the cell J5 needs to be converted within the coding (<funcref=" "/>) which is in each separated by commas.

[Pecoflyer]

Could you please post a sample sheet with some data and expected results? Thx

[ganesang]

Sample file attached here...

Please let me know if anything needed.

[ganesang]

Is this needed for more details?

Please let me know if anything required to clarify.

Thanks

[ArviLaanemets]

Something like this (You can hide helper columns J1 ... J25)

I couldn't make wrap work with formula though.

[ganesang]

Thanks for the help Arvilaanemets!

Is there any other way to directly convert the code in to the particular cell?

I have the excel data which already more columns to scroll and also hyphenated values are cut-off (for ex., J24-26 becomes J24).

Please advise

[ArviLaanemets]

Originally Posted by ganesang

I have the excel data which already more columns to scroll

As I sayd - hide helper columns! Place them as rightmost ones, and hide them. And unless you plan to use the workbook as shared one, define your table as Table like I did won Replacements sheet - then the formulas are added automatically into helper columns whenever new data is added into Table

Originally Posted by ganesang

... also hyphenated values are cut-off (for ex., J24-26 becomes J24).

You have to get rid of such entries. Either replace them with full list, or you'll need some additional helper columns where the entry is at first converted to full list, which is then used as source for further calculations.

Or don't use formulas at all. You design a stored procedure which writes desired result into specified column(s). The procedure is called from event of button on worksheet, or from keyboard shortcut.

[ganesang]

Ok

Thanks for the help!!!!

I will check and get back to you if anything can't unresolved.

Thanks
Ganesan. G