Help in deleting/formating data

Welcome to the forum.

So where in the sample workbook did you mock up what you are aiming for?

Since you are using Excel 365, you have the option of using Power Query which is called Get and Transform Data in your version. It is located on the Data Tab.

1. Highlihgt your data table.
2. Select From Table or Range in PQ
3. In the PQ Editor, select Add Column
4. In the new window type if Text.StartsWith([1],"#") then null else [1])
5. highlight the new column called Custom and filter by unchecking the null box

Here is the Mcode from that action

Please Login or Register  to view this content.

Your sample data results

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	1	4	42	5	Custom
2	JK000004377	30	1600		JK000004377
3	JK000004220	30	1600		JK000004220
4	jk000004223	30	1600		jk000004223
5	ggaegeg	30	1600		ggaegeg
6	jk000003969	30	1600		jk000003969

Sheet: Table1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

and finally the file is attached.

Go with Alan's suggestion - it's what I was going to offer, too.

Maybe this formula: =UPPER(TRIM(SUBSTITUTE(A2,"#","")))
But as Ali said, it is difficult without mock up data.

There is no Sheet 2 attached. Please provide a mocked up solution so that we understand exactly what you are asking for.