Formatting issue

New Contributor


I am currently trying to format a spreadsheet where i require data in one column E.G 01_011_0107_1_1_T, 04_104_0125_1_1_T etc, where each line is a separate code but in another column I am requiring that data to be substituted to remove the underscores, add a 2 to the front of the code and remove some of the numbers from the end. E.G 01_011_0107_1_1_T to become 2010110107. I have worked out how to remove the underscores as a bulk and I have used the substitute function to change it to what I need however I have not been able to get it to do this for ever line without physically typing it into the substitute function for each line. This is very time consuming for over 1000 lines. Can anyone recommend how I would complete this to apply to each line?

Thank you 

1 Reply

@MoniqueMTarampaAssist For anyone who may have similar issues I have fixed it with =SUBSTITUTE(LEFT(O2,11),"_","") which has changed it from 01_011_0107_1_1_T to 010110107 - Still haven't been able to work out how to get a 2 in front of the second number but if anyone has any ideas on that I would be happy to try them