Finding location of multiple spaces in a text.

[lezawang]

Hi
Lets say I have this text:

AAAA BBBB CC DDD EEEE

Is there anyway I can return the location of the spaces (5,10,13,17) in this text? or do I have to nested Search() to find it for me?

Thank you.

 

[DRSteele]

Yes, there is any way. This is one way, but it requires Excel 2016 so that the new function TEXTJOIN can display the results of the array in one cell as text.

Copy downwards the formulas in B2:E2. Columns B, C and D are for academic interest. Note that Column C displays only one character in its array.

	A	B	C	D	E
1	string	length of string (inlc spaces)	creates array of all char	counts the spaces	creates array of the spaces' locations
2	this test contains 4 spaces	27	t	4	5, 10, 19, 21
3	thistestlacksspaces	19	t	0
4	this test begins with a space	30		6	1, 6, 11, 18, 23, 25
5	this test ends with two spaces	32	t	7	5, 10, 15, 20, 24, 31, 32
6	AAAA BBBB CC DDD EEEE	21	A	4	5, 10, 13, 17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet20​

Worksheet Formulas Cell Formula B2 =LEN(A2) C2 =MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1) D2 =SUMPRODUCT(--((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=" ")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=TEXTJOIN(", ",1,IF(((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=" "),ROW(INDIRECT("1:"&LEN(A2))),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[NdNoviceHlp]

Here's a VBA solution. Dave

Sub Test()
Dim InputStr As String, OutStr As String, Cnt As Integer
InputStr = "AAAA BBBB CC DDD EEEE"
OutStr = "("
For Cnt = 1 To Len(InputStr)
If Asc(Mid(InputStr, Cnt, 1)) = 32 Then
OutStr = OutStr & Cnt & ","
End If
Next Cnt
OutStr = Left(OutStr, Len(OutStr) - 1) & ")"
MsgBox OutStr
End Sub

 

[Rick Rothstein]

Here is another VBA solution although this one is a UDF (user defined function) rather than a macro...

[table="width: 500"]
[tr]
	[td]Function SpaceLoc(S As String) As String
  SpaceLoc = Replace(Application.Trim(Join(Application.Transpose(Evaluate("IF(MID(""" & S & """,ROW(1:" & Len(S) & "),1)="" "",Row(1:" & Len(S) & "),"""")")), " ")), " ", ", ")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SpaceLoc just like it was a built-in Excel function. For example,

=SpaceLoc(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[NdNoviceHlp]

That's pretty nice Rick. I sort of understand the evaluate onwards but the rest, I don't. If U have some time to say what's up with transpose, join, trim and replace? Your function doesn't output the end brackets Dave

 

[Rick Rothstein]

Your function doesn't output the end brackets

I decided to respond to your last comment first because that will be the shorter answer. I did not think parentheses were part of what the OP wanted, rather, I thought he used them to show what he wanted as an aside. If the OP did want the parentheses, we would just concatenate them onto the two ends of what is assigned to the function name (SpaceLoc). By the way, my use of parentheses to encase the function name as an aside in order to "explain" what I meant by "function name" is how I thought the OP used them to explain what row numbers he expected back as his answer. Anyway, this what the single code line would look like with them (the parentheses)...

[table="width: 500"]
[tr]
	[td]SpaceLoc = Replace(Application.Trim(Join(Application.Transpose(Evaluate("IF(MID(""" & S & """,ROW(1:" & Len(S) & "),1)="" "",Row(1:" & Len(S) & "),"""")")), " ")), " ", ", ")[/td]
[/tr]
[/table]

That's pretty nice Rick. I sort of understand the evaluate onwards but the rest, I don't. If U have some time to say what's up with transpose, join, trim and replace?

I'll try and I'll use this color coded "exploded" view of that single code line for reference...

SpaceLoc = [B][COLOR="#000000"]Replace([/COLOR][/B]
           [B][COLOR="#EE82EE"]Application.Trim([/COLOR][/B]
           [B][COLOR="#8B4513"]Join([/COLOR][/B]
           [B][COLOR="#0000FF"]Application.Transpose([/COLOR][/B]
           [B][COLOR="#008000"]Evaluate([/COLOR][/B]
           [B][COLOR="#FF0000"]"IF(MID(""" & S & """,ROW(1:" & Len(S) & "),1)="" "",Row(1:" & Len(S) & "),"""")"[/COLOR][/B]
           [B][COLOR="#008000"])[/COLOR][/B]
           [B][COLOR="#0000FF"])[/COLOR][/B]
           [B][COLOR="#8B4513"]," ")[/COLOR][/B]
           [B][COLOR="#EE82EE"])[/COLOR][/B]
           [B][COLOR="#000000"]," ", ", ")[/COLOR][/B]

My explanation will start in the middle and work outward because that is the way the VB compiler processes it (each function call is evaluated and its result is feed to the immediately surrounding function for use when it is processed and so on until the outer most function is processed (at which point the answer is assigned to the function name (SpaceLoc) for return to the calling code (which is either a formula on the worksheet or another VB procedure). Okay, let's start.

The Evaluate function (the red text) is basically an array formula processor although it can also handle non-array formulas as well. Here, we will give it an array formula and it will return an array of values. You already said you kind of figured out the formula the Evaluate function is evaluating, but let me summarize it for other future readers of this thread. Let's assume the cell text being fed to the SpaceLoc function (via the S argument) is "One Two Three Four" (so the final answer should be 4, 8, 14). Note that the length of this text is 18 characters long. Here is what that ugly looking text string argument to the Evaluate function becomes once the substitutions for S are made...

IF(MID("One Two Three Four",ROW(1:18),1)=" ",Row(1:18),"")

so the Evaluate function will iterate the text string from its first character to its last character (character positions 1 through 18)... if it finds a space character, it return outputs the position of the space to the array it returns otherwise it returns the empty text string ("") to the array it returns. The Transpose function takes that vertical array of values and converts it a one-dimensional horizontal array (the array needs to be one-dimensional because the next function in the chain of function is the Join function which can only operate on one-dimensional arrays. Join is a concatenate-type of function which takes each element in the array given to it and makes a text string out of those elements putting the specified delimiter (its second argument which, in this case, is a space character). The result is this text string (I put quote around it so you can see that there are multiple leading and trailing spaces as well as multiple internal spaces)...

"   4    8      14    "

The next function up the chain of functions evokes the worksheet's TRIM function (we do this because unlike VB's Trim function, the worksheet TRIM function removes outer spaces and collapse all interior multiple space down to single spaces. The result of this operation returns this text string...

4 8 14

The last function in the chain replaces those single spaces with a comma space so that the outputted text string returned from the function "looks nice". Okay, that's it... simple, right? Kidding aside, I hope it was somewhat clear to you how my code is working to return what the OP requested.

 

[NdNoviceHlp]

Thanks so much Rick for your time. That was very informative. I hope it's OK to ask a follow-up. I'm not quite clear on this part...
"The Transpose function takes that vertical array of values and converts it a one-dimensional horizontal array"
I don't understand why it is a vertical array and/or what the difference is with a horizontal array. I understand that if U load an array of cells from a row, transpose can be used to place the array into a column and vice versa I assume. Anyways, I hope that lezawang will also appreciate a bit more learning. Thanks again. Dave

 

[Rick Rothstein]

Thanks so much Rick for your time. That was very informative. I hope it's OK to ask a follow-up. I'm not quite clear on this part...
"The Transpose function takes that vertical array of values and converts it a one-dimensional horizontal array"
I don't understand why it is a vertical array and/or what the difference is with a horizontal array.

I do not know what the underlying mechanism is, I only know it works. Normally, when you assign a Transposed range to a Variant variable, you get a two-dimension array back with one of the dimensions having an element range of 1 to 1... and this applies to either vertical or horizontal ranges (which dimension has the 1 to 1 element range varies between the two... but for assigning to the Join function, the following is true (again, as for why, I do not know)..

JoinedVerticalRange = Join(Application.Transpose(VerticalRange), theDelimiter)

JoinedHorizontalRange = Join(Application.Index(HorizontalRange.Value, 1, 0), theDelimiter)

When you use the above code lines, you get at properly delimited text string. A note about the horizontal range code line... the .Value property must be specified for the range argument or it won't work.

 

[NdNoviceHlp]

Rick I am certain that I will be referring back to this post many times as a reference when using these functions in the future. Once again Rick, Thank You for your time. I'm sure that lezawang and others will find this thread informative as well. Have a nice day. Dave