Inserting text within text in a cell

[Bill Doodson]

I have data (from a very large spreadsheet) that has data as shown below in various columns. The data should be as in col A but there is a lot of it in different formats as in B. Is there any way to change the data from B into a new column say E where I can get the data into a leading number of 3 digits, the text with 1 or 2 letters and the remainder into 4 digits with the final text subscript if it is there? Sorry but it looses format a bit here but you will get the picture.

Thanks in advance

Bill

A B

008D0013 08D13
008D0006 08D6
008D0092 08D92
008EJ0002 8 EJ 2
008EJ0003 08EJ3
008EJ0004A 8EJ04A
008EJ0004B 08-EJ-4B
095D0061 95D61

[Pete_UK]

As they are neatly lined up with the correct values in column A, couldn't you just copy the column A values into column E ?

Hope this helps.

Pete

[Bill Doodson]

Hi Pete,

sorry I was not very clear. The columns do not all have the same data in them. I need to get the data in both columns in the same format and then do a duplicate search to end up with a new column eventually with probably 80% of the data as unique from one column and 20% as unique from the other.

Thanks

Bill