Forum Discussion
VBA Copying data in a cell to another
The reason is because I do not want to include the BP in a separate cell. I am looking to put the data
that comes after the BP* and before the end of the ~ in a cell of its own. So the example would be that the script would locate the data between the BP* and the ~ then put it in a blank cell on the sheet. Looking to accomplish the same with the line SN1**2*EA*6~. So in that example anything in between the SN1** and * would go into another cell so this example it would be the 2. Then have the EA go into another blank cell and then the 6 would go into a final cell.
What is the "rule" behind this? How can we know that you do not want to return BP but you do want to return 2? Both are between ** and *.
- I am trying to parse some data. I am trying to figure out how to pull that data after the BP* and before the ~ at the end of the cell. Blow is a few samples:
LIN**BP*6ZS52WA6AA~ - This cell data the VBA code would put the 6ZS52WA6AAin its own cell.
SN1**2*EA*6~ - This cell data would grab the 2 the EA and the 6 and put those into their own cells.
PRF*MW005667***200608~ - This cell data would pull and put the MW005667 in another cell
CLD*1*2***EA~
REF*SE*10000172176167310001~ - Then this cell I would be pulling the 10000172176167310001 and putt that in another cell.
REF*VT*NS100182~ - This one I would be grabbing the NS100182 and putting that into a cell.
HL*3*1*I~
LIN**BP*1VW50SD4AA~ - This is a sample where the process starts over again and I want to put the 1VW50SD4AA in a cell
SN1**3*EA*9~ - Same thing here with the 3, EA and the 9
PRF*MW005667***200608~ - The MW005667 would go into another cell
CLD*1*3***EA~
REF*SE*10000172175887210001~ The pull the 10000172175887210001 and put that into a cell
REF*VT*NS100255~ - Then the NS100255 gets put into another cell.
Trying to automate the process so I do not have to go through thousands of lines to manually copy the info I want to place in separate cells.
I hope this helps.I don't understand the logic of which parts should be extracted and which ones not.
sorry for any confusion. I have attached a sample of the data in Column A and the the data in yellow is what I want to extract into the other fields. I hope the visualization helps.