Results 1 to 5 of 5

Thread: How to convert names in Whatapp message to telephone number

  1. #1
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    11
    Articles
    0
    Excel Version
    2010

    How to convert names in Whatapp message to telephone number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I need help with the following issue:
    I am analyzing Whatapp chat messages. The source file is a .txt file with the following format:
    Date, time stamp, telephone number/name: message text. See below for sample

    9/24/19, 09:48 - James Elm: Feel so sorry for her
    9/24/19, 10:49 - +1 (888) 685-6946: <Media omitted>
    9/24/19, 10:31 - Reny Calm: I feel sorry that she
    9/24/19, 09:48 - +44 7960 853195: The deadline for nominations is October. 4th

    For the records with telephone numbers I managed to converted the telephone number into an area code like 1 (888) and 44.

    Date Time Member Message Hour Week day Day Month Day-Mo Area code
    9/24/19 09:48 +14 7960 853195 Feel so sorry for her 9 Tuesday 24 9 24-9 14
    9/24/19 09:48 +14 7960 853195 The deadline for nominations is October. 4th 9 Tuesday 24 9 24-9 14
    9/24/19 10:31 Reny Calm I feel sorry that she can’t enjoy her youth, and she feels so angry at such a young age. Children should not be watching us. 10 Tuesday 24 9 24-9 ???
    9/24/19 10:49 +1 (888) 685-6946 <Media omitted> 10 Tuesday 24 9 24-9 1 (888)
    9/24/19 12:14 James Ells //gleanerblogs.com/socialimct/?p=4332 12 Tuesday 24 9 24-9 ???


    The issue now is that some records have names instead of telephone numbers e.g James Elm and Reny Calm.
    Any help with an automated mechanism to convert these names to telephone numbers is much appreciated.
    The telephone numbers of the named persons are known.

    Member TelephoneNr
    Reny Calm +599 7 123-4545
    James Ells +44 7960 853195
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    Not possible based on the sample provided - there is no link at all between phone numbers and people, so Excel or PowerQuery will not be able to work out who belongs to which number.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    11
    Articles
    0
    Excel Version
    2010
    AliGW, yes that's what I also taught. In the current data set I solved this issue in PBI by replacing the name with the number, but this is a manual operation that has to be performed with every refresh when new names are added to the data set. Thank you so much for the effort.

  4. #4
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    11
    Articles
    0
    Excel Version
    2010
    I found a solution with the LOOKUPVALUE function in Power BI. For this to work you need a separate lookup file with two columns Names and Telephone number. See Excel and PBI file attached

    I then made a new calculated column: Telnr = IF(LEFT(Chat[Member],1)="+", Chat[Member], LOOKUPVALUE(Lookup[TelephoneNr], Lookup[Member], (Chat[Member])))

  5. #5
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    11
    Articles
    0
    Excel Version
    2010

    Attachment

    Attachment
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •