CRMとデータプラットフォームマーケティングツール

一般的なデータのクリーンアップのためのExcelの数式

私は何年もの間、物事のやり方を説明し、後で自分で調べられるように記録を残すためのリソースとしてこの出版物を使用してきました。 クライアントから渡された顧客データ ファイルは悲惨なものでした。 事実上すべてのフィールドの形式が間違っており、その結果、データをインポートできませんでした。 Visual Basic を使用してクリーンアップを行うための Excel 用の優れたアドオンがいくつかありますが、マクロをサポートしない Office for Mac を実行します。 代わりに、役立つ直接的な公式を探します。 それらのいくつかをここで共有したいと思いますので、活用してください。

数字以外の文字を削除する

多くの場合、システムでは、国番号と句読点を含まない特定の 11 桁の式に電話番号を挿入する必要があります。 ただし、多くの場合、このデータを代わりにダッシュとピリオドを使用して入力します。 ここに素晴らしい公式があります 数字以外の文字をすべて削除する Excelで。 数式はセルA2のデータを確認します。

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

結果の列をコピーして使用できます。 編集>値の貼り付け 適切にフォーマットされた結果でデータを上書きします。

ORを使用して複数のフィールドを評価する

不完全なレコードをインポートから削除することがよくあります。 ユーザーは、必ずしも複雑な階層式を記述する必要はなく、代わりに OR ステートメントを記述できることを認識していません。 以下の例では、A2、B2、C2、D2、または E2 のデータの欠落を確認したいと考えています。 データが欠落している場合は 0 を返します。 それ以外の場合は 1。これにより、データを並べ替えて不完全なレコードを削除できるようになります。

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

フィールドのトリムと連結

データに名フィールドと姓フィールドがあり、インポートにはフルネームフィールドがある場合、組み込みの Excel 関数 Concatenate を使用してフィールドをきれいに連結できますが、必ず TRIM を使用して、名前の前後の空白スペースを削除してください。文章。 いずれかのフィールドにデータがない場合は、フィールド全体を TRIM でラップします。

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

有効なメールアドレスを確認してください

@ と の両方を検索する非常に単純な数式です。 電子メール アドレス ( RFC規格

):

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

姓名を抽出する

場合によっては、問題がその逆であることもあります。 データにはフルネームフィールドがありますが、姓名を解析する必要があります。 これらの数式は、姓と名の間のスペースを検索し、必要に応じてテキストを取得します。 また、A2 に姓がない場合や空白のエントリがある場合にも処理されます。

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

そして姓:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

文字数を制限して追加…

メタディスクリプションをクリーンアップしたいと思ったことはありますか? コンテンツを Excel に取得し、メタ ディスクリプション フィールド (150 ~ 160 文字) で使用できるようにコンテンツをトリミングする場合は、次の数式を使用して行うことができます。 説明をスペースできれいに区切ってから…を追加します。

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

もちろん、これらは包括的であることを意図したものではありません。すぐに始めるのに役立ついくつかの簡単な公式にすぎません。 他にどのような公式を使用していますか? コメントに追加していただければ、この記事を更新する際にクレジットを記載させていただきます。

Douglas Karr

Douglas Karr のCMOです オープンインサイト の創設者であり、 Martech Zone。 ダグラスは、マーテックのスタートアップ企業数十社の成功を支援し、マーテックの買収と投資における 5 億ドルを超えるデューデリジェンスを支援し、企業の販売戦略とマーケティング戦略の実装と自動化を支援し続けています。 ダグラスは、国際的に認められたデジタル変革と MarTech の専門家兼講演者です。 ダグラスは、ダミーズ ガイドやビジネス リーダーシップに関する書籍の著者でもあります。

関連記事

先頭へ戻るボタンに
閉じる

Adblockが検出されました

Martech Zone は、広告収入、アフィリエイト リンク、スポンサーシップを通じてサイトを収益化しているため、このコンテンツを無料で提供できます。 当サイトをご覧になる際は、広告ブロッカーを削除していただければ幸いです。