Cannot extract LEFT or RIGHT data from a cell

Chialiwa

New Member
Joined
Feb 15, 2018
Messages
27
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:

1695943916815.png
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
Data below - See Item Number column:

Book1
ABC
2Item TypereformattedItem Number
3FGFinished good‭000030F‬
4FGFinished good‭000030F‬
5FGFinished good‭000030F‬
6FGFinished good‭000030F‬
7FGFinished good‭000030F‬
8FGFinished good‭000030F‬
9FGFinished good‭000030F‬
10FGFinished good‭000030F‬
11FGFinished good‭000030F‬
12FGFinished good‭000030F‬
13FGFinished good‭000030F‬
I8 Slow Moving and I9 LCM
 
Upvote 0
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,"")
 
Upvote 0
Not sure what solution you found but the code below should remove the unwanted character

VBA Code:
Sub Remove8236()
ActiveSheet.UsedRange.Replace ChrW(8236), "", xlPart
End Sub
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top