formula needed to move data from one row to another and deleting empty rows between data

[RAM72]

Hello Guys

Need help by formula to make data on same line.On converting data2 is above data 1 . I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells up to have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.

What i need is formula to move the data2 on same alignement and to delete the two empty rows between both data.

See annexed file for expected results.I have preference to have a formula much more .

thanks

 

[Luke M]

To get the number:
=LEFT(C4,8)+0

To get the prefix/symbol, confirm this formula as an array using Ctrl+Shift+Enter, not just Enter.
=LEFT(C3,IFERROR(MATCH(1,(CODE(MID(C3,ROW($A$1:INDEX(A:A,LEN(C3))),1))<65)+(CODE(MID(C3,ROW($A$1:INDEX(A:A,LEN(C3))),1))>90),0)-1,LEN(C3)))

Fill down the formulas as far down as needed. This will generate some errors in the cells in between, that's ok for now. With the range of formulas selected, hit ctrl+g (Go to dialogue). Click on Special Cells, then select formulas, and uncheck everything except 'errors'. Hit Ok. Not hit Ctrl and minus symbol. XL will ask you if you want to delete rows or cells, choose to delete the rows. And, you're done.

 

[RAM72]

Hi Luke many thanks for the formula however i followed your instructions all was ok but in doing del rows i got #ref

94017100 CHN
#VALUE!
#VALUE!
95030010 CHN
#VALUE!
#VALUE!
39289097 FR
#VALUE!
#VALUE!
96190021 USA
#VALUE!
#VALUE!
96190021 GRC

ctrl g

94017100 CHN
#VALUE!
#VALUE!
95030010 CHN
#VALUE!
#VALUE!
39289097 FR
#VALUE!
#VALUE!
96190021 USA
#VALUE!
#VALUE!
96190021 GRC

uncheck except erors

#VALUE!
#VALUE!
95030010
#VALUE!
#VALUE!
39289097
#VALUE!
#VALUE!
96190021
#VALUE!
#VALUE!

Final results

#REF! CHN
#REF! CHN
#REF! FR
#REF! USA
#REF! GRC

Advise me followed instructions or could you send me a file to know is where my mistake

many thanks

ram72

 

[Luke M]

Ah, I see. It was my fault, forgot that the number would get deleted. Ok, ammended instructions...

Fill down the formulas as far down as needed. This will generate some errors in the cells in between, that's ok for now. Copy the Range, Paste Special as Values. This will change formulas to constants. With the range of formulas selected, hit ctrl+g (Go to dialogue). Click on Special Cells, then select constants, and uncheck everything except 'errors'. Hit Ok. Not hit Ctrl and minus symbol. XL will ask you if you want to delete rows or cells, choose to delete the rows. And, you're done.

 

[RAM72]

Hi Luke
Bravo its works perfectly, no, all people make mistakes but from our mistakes we all learn to get stronger in sharing on this forum. Every one is happy ,to solve problems it takes hours ,days weeks months ,years but when we reach our goals, every of us benefits.
Again I reiterate my big thanks to you and other members of this forum who contribute to their best to solve problems.

Best Regards

Ram72