Forum Discussion
Text After Delimiter
Dear Experts,
Could you please help in below Query:-
In Attached Excel, I want my data like in Column "B" only the Text after 4 spaces(Item xx), then in Column "C" the text after 8 spaces(so BandCombination), and so on but when I apply the Formula it takes into account all space conditions and not restricts to 4 or 8 ..
Thanks in Advance,
Br,
Anupam
16 Replies
Maybe I'm simplifying things a bit too much (or not understanding the goal here) but if the objective is to pull the text after the leading spaces (which might be 4, 8, 12 spaces, etc). Why not use TRIM?
=TRIM(A2)Patrick2788Thanks Patrick , but the intention is to put the text with different space in different columns
Br,
Anupam
Then I will have to indulge:
=LET( Level, (LEN(List) - LEN(TRIM(List))) / 4, MAKEARRAY( ROWS(List), 7, LAMBDA(r, c, IF(c = INDEX(Level, r), INDEX(List, r), "")) ) )
One option for shortening formulas is to build them from Lambda functions. For example a function to determine how many leading spaces exist might be
= Indentλ(string) where the formula defining Indentλ = LET( initialCharacter, LEFT(SUBSTITUTE(string," ",""), 1), FIND(initialCharacter, string) )PeterBartholomew1Can you please explain this by all the steps you perform? probably in any doc with Screen shots of the the steps we need to perform?
Thanks & Regards
Anupam Shrivastava
The main point is that such formulas only work with Excel 365. The solutions offered by Lambda are not remotely similar to any traditional spreadsheet formula. What a named Lambda function does is allow you to show the intent of a formula without exposing anything of how it is achieved.
In fact, I changed the formula 'Indentλ' to
= LAMBDA(string, LET(initialCharacter, LEFT(TRIM(string), 1), FIND(initialCharacter, string)) )but it does not change anything on the worksheet. I simply posted to suggest a possible avenue to take to make spreadsheet formulas more intelligible, though not always more concise. If you don't have 365, then it is back to something more traditional!
[perhaps not that far back?]
The attached workbook illustrates the type of tricks that are now possible.
select *,replace(supportedBandCombinationList,' ', '</td><td>') a from split_one_cell_to_multiple;peiyezhuSorry , I didn't get it.. Could you please apply the same in Excel sheet and attach the result.
Many Thanks!
Br,
Anupam
http://e.anyoupin.cn/EData/?s=supportedBandCombinationList
this online tool
attach is result of an excel book
Problem is that if a cell begins with 16 spaces, it also begins with 12 spaces and with 8 spaces and with 4 spaces.
In B2:
=IF((LEFT($A132,4*COLUMN()-4)=REPT(" ",4*COLUMN()-4))*(MID($A132,4*COLUMN()-3,1)<>" "),MID($A132,4*COLUMN()-3,1000),"")
Fill to the right to for example H2, then fill down.
- Thanks Hans, is there any other smaller formula we may have?
Using Patrick2788 's idea:
=IF((LEFT($A132,4*COLUMN()-4)=REPT(" ",4*COLUMN()-4))*(MID($A132,4*COLUMN()-3,1)<>" "),TRIM($A132),"")
