• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting letters from a text string

S

Santanu Chatterjee

Member
Dear All,

Could you please help me with this problem. I have the first name and last name in one cell with a space in between. I would like to extract the first two letters from the first name and first two letters from the last name. How do I do that?

Regards,
Santanu
 
At the other extreme
Code: 
= PersonIDλ(fullNames)

where the Lambda function is given by

= LAMBDA(fullName,
    LET(
      space,     " ",
      Concatλ,   LAMBDA(n, CONCAT(n)),
      firstName, TEXTBEFORE(fullName, space),
      surname,   TEXTAFTER(fullName, space, -1),
      nameArr,   LEFT(HSTACK(firstName, surname),2),
      BYROW(nameArr, Concatλ)
    )
  )
What does it achieve that @vletm's formula doesn't? Very little.
1. It copes with middle names by omitting them.
2. It takes a list of names and produces the person IDs as a dynamic array
3. It can be reused elsewhere within the workbook.
Is it worth the complexity? Probably not, unless this is the approach taken to all other elements of the solution.
Feel free to disagree.
 
A word of caution. The functions I use in every Excel solution I build are only available in Excel 365 and 2021. In this case, the presence of TEXTBEFORE, TEXTAFTER and HSTACK currently requires an insider beta version of Excel though, in due course, the functionality will be rolled out more generally. Meanwhile go with @vletm's solution unless you have to identify 3rd or 4th names.
 
Peter Bartholomew
Three sentences from #1:
I have the first name and last name in one cell with a space in between.
I would like to extract the first two letters from the first name and first two letters from the last name.
How do I do that?
 
vletm said:
Peter Bartholomew
Three sentences from #1:
I have the first name and last name in one cell with a space in between.
I would like to extract the first two letters from the first name and first two letters from the last name.
How do I do that?
Click to expand...

We can use :
LEFT(text, [num_chars])
ex: =LEFT(A2,4)

RIGHT(text, [num_chars])
ex: =RIGHT(A2,4)

MID(text, start_num, num_chars)
ex:=MID(A2,6,3)
 
You must log in or register to reply here.
Back
Top