-2

Requesting someone to help on vba/Macro code. I want to set number in each sentence for each cells.Attached screenshot for detail.

Below are the input and expected output. I am new to Macro so please help some one.

enter image description here

Pᴇʜ
  • 45,553
  • 9
  • 41
  • 62
Imran Azam
  • 89
  • 8
  • 3
    You might benefit from reading [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) • Note that this is no free code writing service. So you should do some research first and try something on your own then come back with your attempt (and what you have researched) to ask a good question (see [ask]). – Pᴇʜ Jun 08 '20 at 11:48
  • I know this is not free code writing service. I tried to achieve desired output but could not. This is the platform where we connect and help each other. I am not asking entire code just logic i want. By the way thanks for reading my question. – Imran Azam Jun 08 '20 at 11:55

1 Answers1

3

You could also use a formula and avoid VBA if prefered:

enter image description here

Formula in B1:

=TEXTJOIN(CHAR(10)&CHAR(10),1,IFERROR(ROW(A$1:INDEX(A:A,LEN(A1)))&"."&FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s[node()]"),""))

Confirm through CtrlShiftEnter

Using XPATH we return all nodes that have any underlaying value. If you want to know more about the use of FILTERXML to change around substrings, you may find this an interesting read too.

Note: For the shown results you'd need to enable text wrapping on column B. On the Start tab under Alignment click Text Wrap

JvdV
  • 41,931
  • 5
  • 24
  • 46
  • Thank you so much JvdV – Imran Azam Jun 08 '20 at 12:16
  • Thanks for giving reference for better understanding on Filterxml. I don't know why i am getting output in single row like this 1.TestA 2.TestB 3. TestC. In you attached screenshot output is as expected. What i am doing, i just apply formula and pressed ctlr+shift+Enter.....Am i doing any wrong? – Imran Azam Jun 08 '20 at 13:13
  • You just need to enable text wrapping on the column =) @ImranAzam – JvdV Jun 08 '20 at 13:14