@spyroskonst
Once again i'm speechless. The quality of explanation, including a workbook to test everything, is outstanding.
@alfayezamj
Leila, you have no idea how long i have suffered from this spacing issue specially with numbers! you are going straight to heaven for making our life easier :) THANKS A LOT. i have been following your channel & becomeing a huge fan.
@christyamaranathfernando8212
Thank you so much. Your tutorials are so useful, very easy to follow and understand.
@mtroanoke
You can also use the CLEAN function in combination with TRIM to get rid of the non-printable characters that appear to be spaces. EDIT - after seeing another comment, I should clarify that it seems CLEAN will not work for CHAR(160) but it does work for many others.
@BillHester2011
Wow! Just Wow! Thank you for being such a marvellous human being Leila. After struggling all week to figure this out I could almost cry after listening to your wonderful voice explaining the way forward in such perfectly simple terms. Thank you!
@kahhengyeong7947
Another OMG from me! I thought I knew the trim function and nearly skipped this video. Not only i learnt so much more but also learnt not to skip your videos!!!
@harveycovey2215
Here is a VBA Function I put together to use as a TRIMALL() in spreadsheets. It has the added umph of allowing a range to be passed in to generate a sentence of values from a range with an optional delimiter parameter for a delimited string.
'FUNCTION TRIMALL
'PARAMS:
' text
' delim (optional for ranges, defaults to single-space)
'RETURNS:
' If the value in the 'text' parameter is a string, the value
' with non-breaking spaces (chr(160)) replaced by standard spaces (chr(32))
' double-spaces removed and trimmed of leading and trainling spaces.
'
' Additionally, if the value in 'text' is a range,
' a string of values with chr(160) replaced by chr(32) and each cell in the range
' trimmed of double-spaces with leading and trailing spaces removed and
' separated by a space or optionally-provided delimiter ('delim').
'
Function TRIMALL(text As Variant, Optional delim As String = " ")
Dim s As String 'result string of text
Dim t As String 'temp string
'if the variant of text is a Range
If (VarType(text) = 8204) Then
Dim r As Range
Dim c As Range
Set r = text
For Each c In r
'replace non-breaking spaces
t = Trim(Replace(c.Value, Chr(160), " "))
'since it is a range, add a delimiter between each trimmed value
If s = Empty Then s = t Else s = s & delim & t
Next c
Else
'replace non-breaking spaces and trim the text value
s = Trim(Replace(text, Chr(160), " "))
End If
'check for instances of double-spacing and trim those out
While InStr(s, " ") > 0
s = Replace(s, " ", " ")
Wend
'finally, trim the result and return it
TRIMALL = Trim(s)
End Function
@romualdephraimbeda1736
The way you alwaaaaayyyyyyyssss save my life. Only GOD can pay you back. I was trying to fix that for months. I was reduce to find a VBA code until you come up with the LIGHTS. The only Excel Angel on earth :)
@nataliamunoz3089
Thank you for this video! so easy to understand and very well explained.
@IvanLopez-rv7wh
this was a super help for me i used this today & fixed a lot of issues with spaces on my spreadsheet. thanks so much for your time and detication for these tutorials they are a really help for a lot of people worldwide !!!! awesome work !!!
@eddywijaya2204
Straight to the point. I've been searching this method for an hour. Thanks for the tricks!
@johnburton4577
That's helped clear up some annoyances I've had before copying data from / referring to another spreadsheet. Many thanks.
@ariel-zight
Leila, you rock! I've tried all the quick and easy ways of solving this issue and all failed. Finding the right character did the trick. Thanks a ton! Subscribed!
@williamreith1186
I wish you'd made this video last week because I encountered exactly this problem on Sunday whilst reconciling expenses. Based on topic expertise, delivery, A/V quality, visuals, and narration, you are the best teacher on YouTube bar none. Furthermore, your lessons and courses yield excellent, professional results. I look forward to your Thursday morning installments and have watched your accounting videos since they began a couple of months ago. Great perspective. Thanks many times over.
@TheHgfj
Bundles of video out there on youtube, but this one covers the true problem statement and solution. Appreciated. Keep sharing such videos.
@eddiemorgan1557
You are my hero! Leila G. I asked Google how to get rid of spaces in Excel and you showed up and solved the problem.
@BMRSKI-Excel-Initiation
You are the best .. i always found a solution in your videos..thank you and god bless you.
@jimkutamamman7604
Thanks Leila, you have always understand the problems before I encounter them.
@ansarulalikhan634
This video just saved my tons of time. Love you so much ❤️
@LeilaGharani