Adjust spaces in cell

[YasserKhalil]

Hello everyone

In column F I have some data and the data in each cell is in two parts ..
In the yellow cells the data are adjusted to look in neat format ..
How can the spaces adjusted between the two parts to be like the yellow cells?

Thanks advanced for help

* I have posted the thread on this link too
https://www.excelforum.com/excel-progra ... -cell.html" onclick="window.open(this.href);return false;

[Doc.AElstein]

Hi Yasser.
I do not know how to do this with a formula.
Here are some ideas to do it in VBA
Run code Sub Tests() and look at the results in immediate window.
You may be able to adapt some of the ideas to suit you
Code:

Code: Select all

Sub Tests()
 Call LSetFuelandKM(Range("F4").Value)
 Call LSetFuelandKM(Range("F5").Value)
 Call LSetFuelandKM(Range("F6").Value)
End Sub
Sub LSetFuelandKM(ByVal RngVul As String)
Debug.Print
Dim KM As String: Let KM = Mid(RngVul, InStr(1, RngVul, "KM ", vbBinaryCompare)): Debug.Print KM
Dim FjorlUnly As String: Let FjorlUnly = Replace(RngVul, KM, "", 1, 1, vbBinaryCompare): Debug.Print FjorlUnly & "|"
Dim TabulatorSyncrenator As String: Let TabulatorSyncrenator = String(87, " "): Debug.Print "|" & TabulatorSyncrenator & "|"
 LSet TabulatorSyncrenator = FjorlUnly: Debug.Print TabulatorSyncrenator & "|"
Dim Bohf As String: Let Bohf = TabulatorSyncrenator & KM: Debug.Print Bohf
Debug.Print Bohf
Debug.Print
End Sub

Typical results:

Code: Select all

 
KM 2606272
Fuel 36.39 Ltr Penzin 92                                                                         |
|                                                                                       |
Fuel 36.39 Ltr Penzin 92                                                               |
Fuel 36.39 Ltr Penzin 92                                                               KM 2606272
Fuel 36.39 Ltr Penzin 92                                                               KM 2606272


KM 2608806
Fuel 25.57 Ltr * 5 LE Penzin 92 - Mobil                                                   |
|                                                                                       |
Fuel 25.57 Ltr * 5 LE Penzin 92 - Mobil                                                |
Fuel 25.57 Ltr * 5 LE Penzin 92 - Mobil                                                KM 2608806
Fuel 25.57 Ltr * 5 LE Penzin 92 - Mobil                                                KM 2608806


KM 2613589
Fuel 34.73 Ltr * 6.75 LE Penzin 92                                         |
|                                                                                       |
Fuel 34.73 Ltr * 6.75 LE Penzin 92                                                     |
Fuel 34.73 Ltr * 6.75 LE Penzin 92                                                     KM 2613589
Fuel 34.73 Ltr * 6.75 LE Penzin 92                                                     KM 2613589 

Alan

Ref: string stuff
http://www.excelfox.com/forum/showthrea ... er-strings" onclick="window.open(this.href);return false;

[YasserKhalil]

Thanks a lot Mr. Alan
It is not clear for me in the immediate window
Can you please put the output in another column (may be H is suitable(
Thanks a lot

[YasserKhalil]

The post #7 in the other link is very good solution

[Doc.AElstein]

If you are in the VB Editor and you use keyboard combination Ctrl+g , then you can see the immediate window.
Then step through the code using F8 and it should be obvious what the code does...

But anyway
Same thing as a function

Code: Select all

Sub FunkyLSetFuelandKM()
 Let Range("H4").Value = LSetFuel_KM(Range("F4").Value)
 Let Range("H5").Value = LSetFuel_KM(Range("F5").Value)
 Let Range("H6").Value = LSetFuel_KM(Range("F6").Value)
 Columns("H:H").AutoFit
End Sub
Function LSetFuel_KM(ByVal RngVul As String) As String
Debug.Print
Dim KM As String: Let KM = Mid(RngVul, InStr(1, RngVul, "KM ", vbBinaryCompare)): Debug.Print KM
Dim FjorlUnly As String: Let FjorlUnly = Replace(RngVul, KM, "", 1, 1, vbBinaryCompare): Debug.Print FjorlUnly & "|"
Dim TabulatorSyncrenator As String: Let TabulatorSyncrenator = String(87, " "): Debug.Print "|" & TabulatorSyncrenator & "|"
 LSet TabulatorSyncrenator = FjorlUnly: Debug.Print TabulatorSyncrenator & "|"
Dim Bohf As String:  Let Bohf = TabulatorSyncrenator & KM: Debug.Print Bohf
 Let LSetFuel_KM = Bohf
End Function

Edit: Note what BadlySmellyBuoy said about the font type... that is a good point that i did not know about ( https://www.excelforum.com/excel-progra ... ost5031534" onclick="window.open(this.href);return false; )

[YasserKhalil]

Thank you very much Mr. Alan
I will try to study the code to try to make use of it

[Doc.AElstein]

I think the only thing you may not be aware of is the LSet Statement.
( Someone who was familiar with older Visual Basic things made me aware of many of these things ( https://www.excelforum.com/word-program ... ost4590792" onclick="window.open(this.href);return false; ) )

So, to explain, say my variable is x = “123456789”

Code: Select all

     “123456789”     

If I then do like …_
LSet x = “Foops”
_... then my result will be x is

Code: Select all

    “Foops    “   

Do you see what has happened? – x now has “Foops” at the left hand side of a string of 9 characters - The string keeps the same length as before, that is to say, x remains at a length of 9 characters. The extra characters are always made up of spaces,

It does not matter what the original characters in x are. They can even be 9 spaces, like x = String(9, " ")

Code: Select all

   “         “  

LSet x = “Foops” will set “Foops” at the left hand side of a string of 9 characters. The extra characters are always made up of spaces, - it makes no difference what the original 9 characters in x were
Here once again, the link to things like LSet http://www.excelfox.com/forum/showthrea ... er-strings" onclick="window.open(this.href);return false;

Alan

_.___

Here one way to adapt the idea:

Code: Select all

 Sub Testy()
 Let Range("H4:H23").Value = SHimfGlified_LSetFuel_KM(Range("F4:F23"))
 Columns("H:H").AutoFit
End Sub
Function SHimfGlified_LSetFuel_KM(ByVal RngIn As Range) As Variant
Dim Rng As Range, arrAut() As String: ReDim arrAut(RngIn.Row To (RngIn.Rows.Count + RngIn.Row) - 1, 1 To 1)
    For Each Rng In RngIn
    Dim KM As String: KM = Mid(Rng.Value, InStr(Rng.Value, "KM "))
    Dim F As String:  F = Replace(Rng.Value, KM, "", 1)
    Dim TS As String:  TS = String(87, " ")
     LSet TS = F
     arrAut(Rng.Row, 1) = TS & KM
    Next Rng
 Let SHimfGlified_LSetFuel_KM = arrAut()
End Function

Edit Here also a great solution
https://www.excelforum.com/excel-progra ... ost5032667" onclick="window.open(this.href);return false;

[YasserKhalil]

That's great. Thank you very much for great help