We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Deleting Characters in Excel
Options

choyaa
Posts: 226 Forumite
in Techie Stuff
Hi,
Apologies for yet another Excel query, I know I was slated recently, however I would appreciate your help. I have sent up the following formula and it works perfectly, however I want to delete the two end characters of the returned value.
=(VLOOKUP(VLOOKUP(B30,'tab1'!$A:$JT,MATCH($F$29,'tab1'!$1:$1,0),0),'tab2'!$D:$E,2,FALSE))
However I have tried the following simple code but it's returning "#VALUE," can anyone explain where I have went wrong?
=Right((VLOOKUP(VLOOKUP(B30,'tab1'!$A:$JT,MATCH($F$29,'tab1'!$1:$1,0),0),'tab2'!$D:$E,2,FALSE)),-2)
Apologies for yet another Excel query, I know I was slated recently, however I would appreciate your help. I have sent up the following formula and it works perfectly, however I want to delete the two end characters of the returned value.
=(VLOOKUP(VLOOKUP(B30,'tab1'!$A:$JT,MATCH($F$29,'tab1'!$1:$1,0),0),'tab2'!$D:$E,2,FALSE))
However I have tried the following simple code but it's returning "#VALUE," can anyone explain where I have went wrong?
=Right((VLOOKUP(VLOOKUP(B30,'tab1'!$A:$JT,MATCH($F$29,'tab1'!$1:$1,0),0),'tab2'!$D:$E,2,FALSE)),-2)
0
Comments
-
Firstly, if you want to remove the end characters, you need the LEFT function, not the RIGHT function.
Secondly, you can't just use -2 as that would be a nonsense value for either function.
You need to figure out how long the returned string is, subtract 2 from it and return the rest.
The correct function would be:=
=left((VLOOKUP(VLOOKUP(B30,'tab1'!$A:$JT,MATCH($F$29,'ta b1'!$1:$1,0),0),'tab2'!$D:$E,2,FALSE)),len((VLOOKUP(VLOOKUP(B30,'tab1'!$A:$JT,MATCH($F$29,'ta b1'!$1:$1,0),0),'tab2'!$D:$E,2,FALSE))-2)
That looks about right, but you'll need to check the placement of the parentheses.:wall: Flagellation, necrophilia and bestiality - Am I flogging a dead horse? :wall:
Any posts are my opinion and only that. Please read at your own risk.0 -
can anyone explain where I have went wrong?
GONE wrong
/pedant
Why not just post the whole spreadsheet up here and get someone to do it all at once instead of cell by cell for you..
Orrrr ,
https://www.amazon.co.uk/Advanced-Excel-Essentials-Jordan-Goldmeier/dp/1484207351
http://www.pitman-training.com/courses/microsoft-excel-expert?campaign=google_ppc&pos=1t3&nw=g&awloc=1007105&gclid=CIz504mr_c4CFSMW0wod9EUDwA
http://www.mrexcel.com/forum/0 -
Andy I 've already offered various sites for the OP to learn Excel and vba - clearly not taken up.
OP I have no gripes (and didn't and wouldn't slate you) for people trying to learn by doing it themselves. That is the best way to learn because once figured out it stays with you for ever (well quite a long time) but there are better sites than MSE for Excel queries and they often have multiple answers if you use the search facility for your particular problem. Sometimes a little sideways logic is required in phrasing the question but the forum members are very knowledgeable. Try some of the links suggested in previous posts and by Andy in post #3. Good luck.0 -
I agree with the advice given here and also do not think you have been slated. Just good posters giving good advice. You might make faster progress if you heed the advice.
Formula understanding gets progressively more difficult the longer and more complicated it gets hence the advice to do it in intermediate steps and using meaningful range names - all to make them more understandable and trace the errors.
Consider your current problem. Return the results of your formula into a cell (perhaps onto a column that you can hide or onto a new sheet, give that cell a range name such as 'lookup_result'
Your truncation of characters can then work on that cell such as
=left (lookup_result, 2)
but more correctly as posted above
=left(lookup_result, Len(lookup_result)-2).
Much easier to understand than a long formula and more obvious where any error is introduced.
If you post online in a thread you should however, as readers cannot see your workbook, explain what and where the range name is. Better still share your workbook, even a sanitised version if you wish.
Unless you made an error in the lookup bits of your long formula (easy to do inadvertently) it should be obvious that it was the delete attempt that caused the error. Excel has good formula help if you instigate it either by the icon next to the cell formula of via F1 button that then tells you what to put in each part of the formula
.......well it did in older versions and I can't see even Microsoft getting rid of Help!! .......then again......0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.9K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.9K Work, Benefits & Business
- 598.7K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.1K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards