[EXCEL] Extracting Telephone Numbers with Wildcard # in Format ### - ### - ### from Cell Data

How can I get the text that interests me from the cell. I mean exactly something like this:

I have a sequence of data in my cells (name, surname, address, city) including telephone numbers. I have to get them to the next cell. How can I do this with the wildcard "#" ?? Phones are in the form: "### - ### - ###"

If you use a wildcard character, I don't know if you can make it. Maybe try to go crazy with the substring function. The only question is whether the cell from which you will retrieve data has a predictable format (always the same data order) or whether you need to enchant something.

Maybe give me an example of the data you are working on.

Pablo

The problem is that the phone number is in a different place, sometimes it is preceded by a semicolon, sometimes not . From what I have already read about wildcards, you can enter what the searched phrase looks like and get the data. However, from the examples I found, this is a column search and selecting them has nothing to do with retrieving a given piece of text.

I found an example like this:


but i have no idea how to apply it in my code.

Can you summarize what we can expect about the data you receive?
1. Is there any field separator?
2. Is the phone number always in the same format (length, separators etc?) Or can you write it with a wildcard character?

As far as I understand what you wrote so far, the number is written arbitrarily in various places in the sequence and not always separated by a separator. Yes?

Send some representative examples, something I'll be able to think about (anonymize the data)

Pablo

one cell looks like this:

phone :###-###-### first name surname address

different

name surname address ### - ### - ###

or a telephone in front of the address

there is also a bank account number or two telephone numbers separated by a comma

I also found such a function but I cannot use it:

I made a small formula in excel, I hope it will help you:

= FRAGMENT.TEXT (A1, SEARCH ("??? - ???"; A1; 1); SEARCH (","; A1, SEARCH ("??? - ???"; A1; 1)) - SEARCH ("??? - ???"; A1; 1))

The formula finds the string ??? - ??? anywhere in the text. and treats it as the beginning of a phone number and then cuts it to the nearest comma. You can easily modify this to cut to the nearest space or other separator. If the separators are different in different lines, make an additional column to which you load the data using the replace function and thus all separators can be reduced to a standard separator.

For the formula I sent to work, it is important that each phone number has a predictable structure (### - ### - ###).

If it is as you write:



Worse, if there is a city with a bridge in front of the number Bielsko-Biała for this example.

Works perfectly thanks. And how can I modify it to get the account number still ?? (in the form of ## #### #### - 32 characters including spaces). I tried on my own but I have some heresies.

some cells I have:

VAT INVOICE NR F / 000078/05 BARA TRAN Jan Kowalski 535-538-121 address city ## #### #### #### #### #### #### PKO O / Rzeszów

When I try with the account, it gets me a string starting from 05 BARA TRAN

and how do I enter "?? ???? ????" I have a # ARG #

How is the account number saved?
For example, if "11 1111 1111 1111 1111 1111 1111" then enter "?? ???? ???? ???? ???? ???? ????" as the text in the formula. this matches the character structure of the account number and should be OK. If it is written without spaces, it can be a problem because the only wildcards that search.text can accept are * and?. Alternatively, you can then search for the text " ?????????????????????????? " where is your value separator.

Example:
Text: Name, Surname, 323-323-33, Description, 33 3333 3333 3333 3333 3333 3333, description
Phone formula: = FRAGMENT.TEXT (A1; SEARCH ("??? - ???"; A1; 1); SEARCH (","; A1; SEARCH ("??? - ?? ? "; A1; 1)) - SEARCH (" ??? - ??? "; A1; 1))
Account number formula: = FRAGMENT.TEXT (A1; SEARCH ("?? ????"; A1; 1); SEARCH (","; A1; SEARCH ("?? ????" "; A1; 1)) - SEARCH (" ?? ???? "; A1; 1))

Regards

I typed



and i have blank as result

and I can see where the problem is, I have data after a space, you entered the data as a sequence and are separated only by a comma, so it works for you.

And I'm not surprised. Your form cuts from spaces before the number to spaces before the account number
Enter: = FRAGMENT.TEXT (A1; SEARCH ("?? ???? ???? ???? ???? ???? ????"; A1; 1) +1; 32 )


And when you do the final version, I recommend the formula:
= IFERROR (; "") This way, you won't get #ARG, but a blank cell in your worksheet.

It works thanks for your help for two days with that, now I just put it in the macro :)


Error pops up: #NAME?

tohimon wrote:

Error pops up: #NAME?

So you have a typo in the function name, or if you are typing a formula into a macro, you have to use the English function names.

It's best to use macro recording and type in the formula I gave you. This way, the English equivalents will fit into the macro.

I have already corrected I am using Excel 2003 where there is no formula if error. I had to replace it with IF (ISERROR ()) and now it works as it should. Thanks again for your help