Text wrap the first word of a cell instead of the last.

[JasonTheGreat]

i Have English text in a single cell that is actually read right to left. It is aligned to the right and If the text is too much it should wrap the word furthest to the left.

I’ve played with the “text direction” in the “alignment settings” but it always wraps the word furthest to the right.

How can I wrap the text furthest to the left?

 

[jasonb75]

If it is even possible, it would mean changing your system settings to a language that supports right to left text.

That would mean having everything on your computer formatted as right to left, not just one excel sheet and that the sheet would still appear in normal left to right format on a different computer.

The other problem that you will encounter is that there are no languages which use right to left text with the latin alphabet, so it is unlikely that your text will be displayed properly.

 

[JasonTheGreat]

Does anyone with Apple Numbers or Google Sheets know if this is possible? I am hoping another spreadsheet program can do this.

 

[JasonTheGreat]

Is it possible to edit how text wrap works? I mean ripping it’s actual code apart and putting it back together. It must be possible to recreate text wrap without actually using the words “text wrap in vba.”

I’ve been working on a project for years in excel and this is the only thing stopping me from publishing.

 

[jasonb75]

All applications will be the same, not just spreadsheets. It is a language setting, not a format setting.

To do what you want in any application would need the content of the cell to be re-ordered based on the cell width, i.e. moving the first word on the left to the right of the string.

 

[JasonTheGreat]

All applications will be the same, not just spreadsheets. It is a language setting, not a format setting.

To do what you want in any application would need the content of the cell to be re-ordered based on the cell width, i.e. moving the first word on the left to the right of the string.

I found that Zulu is written right to left and shares the English alphabet but Zulu is not an option for excel.
Wrap text in Hebrew language settings doesn’t make sense. Instead of ending with beginning or end you end up with just the center...pic below.
If possible I would really like to break wrap text apart in vba and rewrite it. Even in vba wrap text just says “wrap text.” I am stuck.
If I wrap manually that’s 14,000 individual lines I’d have to go through one by one. What should I do?

 

[Peter_SSs]

i Have English text in a single cell that is actually read right to left.

I have no idea whether that means like A2 or A3 below or something else altogether, but see if this work-around is any use.

With normal wrap text if you subsequently change the width of a column the wrapping automatically readjusts. That is not the case with my code below. My code also currently rests with you deciding on an "appropriate" value for the 'Const' line in the code & that will relate to your column width and font used.

As you can see my code puts the results in the adjacent column but you could place it back over the original data if you want.

Test with a copy of your workbook.

Sub WrapRightToLeft()
  Dim a As Variant
  Dim s As String, sParts() As String
  Dim k As Long, i As Long
  
  Const CharsPerLine As Long = 20     '<-Change to suit
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = Application.Trim(a(i, 1))
    If Len(s) > 0 Then
      k = 0
      ReDim sParts(1 To Len(s) / CharsPerLine + 1)
      Do Until Len(s) = 0
        k = k + 1
        sParts(k) = LTrim(Split(Right(Space(CharsPerLine) & s, CharsPerLine + 1), " ", 2)(1))
        s = Trim(Left(s, Len(s) - Len(sParts(k))))
      Loop
      ReDim Preserve sParts(1 To k)
      a(i, 1) = Join(sParts, vbLf)
    End If
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

My sample data in column A, code results in column B.

JasonTheGreat 2020-04-09 1.xlsm
	A	B
1	Data	Data
2	dog lazy the over jumps fox brown quick The	fox brown quick The lazy the over jumps dog
3	god yzal eht revo spmuj xof nworb kciuq ehT	xof nworb kciuq ehT yzal eht revo spmuj god
4
5	fifteen fourteen thirteen twelve eleven ten nine eight seven six five four three two one	four three two one eight seven six five eleven ten nine thirteen twelve fifteen fourteen
Sheet1