Cannot extract LEFT or RIGHT data from a cell

[Chialiwa]

We have a data dump that brings our item numbers into the cells as "000043F". I am trying to write a formula where if the right character is F, so one and so forth. The problem is no LEFT or RIGHT formula works on this cell at all. I have tried TRIM, TEXT and nothing works. Usually this happens when there is an extra space, but in this case there are NO exptra spaces in the front or end. However, if I add one space to the end but hit backspace twice it lands me at the very end. It's almost like there is a hidden space in there??? What can I do? Example of what it looks like below:

 

[duggie33]

Hi,

Please provide some sample data using the MrExcel tool called “XL2BB”. It lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[Chialiwa]

Data below - See Item Number column:

Book1
	A	B	C
2	Item Type	reformatted	Item Number
3	FG	Finished good	‭000030F‬
4	FG	Finished good	‭000030F‬
5	FG	Finished good	‭000030F‬
6	FG	Finished good	‭000030F‬
7	FG	Finished good	‭000030F‬
8	FG	Finished good	‭000030F‬
9	FG	Finished good	‭000030F‬
10	FG	Finished good	‭000030F‬
11	FG	Finished good	‭000030F‬
12	FG	Finished good	‭000030F‬
13	FG	Finished good	‭000030F‬
I8 Slow Moving and I9 LCM

 

[Chialiwa]

I want to specify that if I do a right,2, it will pull the F but I need it in this formula below and I can't use just F since it's like an F with a space:

=IF(AND(RIGHT(D107,1)="F",LEFT(A107,3)<>"CDE",LEFT(A107,3)<>"CDW",LEFT(A107,3)<>"CFE",LEFT(A107,3)<>"CFW"),I107-R107,"")

 

[Chialiwa]

I found the solution thank you all!

 

[MARK858]

Not sure what solution you found but the code below should remove the unwanted character

Sub Remove8236()
ActiveSheet.UsedRange.Replace ChrW(8236), "", xlPart
End Sub

 

[Fluff]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.